Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Column Total at the end of SQL Pivot query

I have generated the following SQL Server 2008 pivot, and it gives me desire result. I want to add total column at end of pivot, where I'm finding it difficult.

Please find the SQL I'm using for pivot

Select * from (
     Select Case when (podocstatus = 'CL') then 'Closed PO'
         when (podocstatus = 'OP') then 'Open PO'
         when (podocstatus = 'SC') then 'Short Closed PO'   
    end as POStatus, 
    YEAR(podate) as [Year], YEAR(podate) as [poyear] , LEFT (datename(Month,podate),3) as [pomonth]
    From PO_order_hdr
    Where podocstatus IN ('SC','CL','OP')
    ) as POnumber
PIVOT
(
    Count(poyear)
    FOR [pomonth]  IN (Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)

)as PVT

Please help.

like image 850
Saifuddin Avatar asked Dec 18 '13 06:12

Saifuddin


People also ask

How do I total a pivot table in SQL?

You can find Total for root_cause and environment_name using ROLLUP . RNO_COLTOTAL - Logic to place Total in last column, since the columns Tsc , Unkn will overlap the column Total when pivoting, since its ordering alphabetically.

How do I add a total row in SQL?

In order to calculate a subtotal in SQL query, we can use the ROLLUP extension of the GROUP BY statement. The ROLLUP extension allows us to generate hierarchical subtotal rows according to its input columns and it also adds a grand total row to the result set.

How do you total a column in SQL?

The SUM() function returns the total sum of a numeric column.

How do I sum a dynamic column in SQL Server?

Here's one way to do it: You can use INFORMATION_SCHEMA. COLUMNS View to get all the column names and put them in a temp table. Next, create a temp table to store your sums. You can then use dynamic sql and a loop to sum each column.


1 Answers

The easiest solution would be to simply do something like this:

Select *,
    Jan + Feb + Mar + Apr + May + Jun + Jul + Aug + Sep + Oct + Nov + Dec AS [Total]
from
    ...

An alternative solution for the general case, would be to use a subselect. Move your inner query into a CTE, to make things a bit easier to work with:

WITH POnumber (POStatus, [Year], [poyear], [pomonth]) AS
(
    Select sase when (podocstatus = 'CL') then 'Closed PO'
         when (podocstatus = 'OP') then 'Open PO'
         when (podocstatus = 'SC') then 'Short Closed PO'   
    end as POStatus, 
    YEAR(podate) as [Year], YEAR(podate) as [poyear] , LEFT (datename(Month,podate),3) as [pomonth]
    From PO_order_hdr
    Where podocstatus IN ('SC','CL','OP')
)
select *,
    -- Subselect that counts the total for the given status and year:
    (select count([Year]) from POnumber T 
     where T.POStatus = PVT.POStatus and T.poyear = PVT.poyear) as [Total]
from POnumber
PIVOT
(
    Count(poyear)
    FOR [pomonth]  IN (Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)
)as PVT
like image 125
Dan Avatar answered Sep 28 '22 18:09

Dan