CODEWITHSUNDEEP
sqldatabasesql-server-2008-r2pivot
C Sharper
C Sharper

Reputation: 8626

Accommodate sum of rows in pivot query table

I have following table:

enter image description here

I want to get result as :

mon  QDBCount   MINISTERIALECount  Sum

Jan    5           3                8

Feb    4           9               13

Mar    6           5               11

.
.
.

Note: QDBCount and MINISTERIALECount is number of times these two arrived for that particular month in above table

For this i have made following query:

select * from
(
select convert(char(4),[Data creazione pratica],100) mon  , 
[Cod# prodotto],count([Cod# prodotto]) cnt
from dbPratiche 
where Compagnia='GENERTEL'
group by [Cod# prodotto], convert(char(4),[Data creazione pratica],100)


) T 
pivot
(
 max(cnt)
 for [Cod# prodotto] 
 in ([QDB],[MINISTERIALE])
)p

And i am getting result:

enter image description here

I want to take sum of 2nd and 3rd column in it for each row(as mentioned above in expected result). But not able to take.

How can i make changes in query? What can be the query

Upvotes: 3

Views: 177

Answers (2)

PeterRing
PeterRing

Reputation: 1797

Well, this is kinda lame but:

WITH ctablee (mon, qdb, ministeriale) 
     AS (SELECT "columns what you use" 
         FROM   (SELECT CONVERT(CHAR(4), [data creazione pratica], 100) mon, 
                        [cod# prodotto], 
                        Count([cod# prodotto])                          cnt 
                 FROM   dbpratiche 
                 WHERE  compagnia = 'GENERTEL' 
                 GROUP  BY [cod# prodotto], 
                           CONVERT(CHAR(4), [data creazione pratica], 100)) T 
                PIVOT ( Max(cnt) 
                      FOR [cod# prodotto] IN ([QDB], 
                                              [MINISTERIALE]) )p) 
SELECT mon, 
       qdb, 
       ministeriale, 
       ministeriale + qdb 
FROM   ctablee

Upvotes: 1

Kiril Rusev
Kiril Rusev

Reputation: 753

Since the number of Products that you have is limited, you can employ an alternative approach by using subqueries:

;with cte as (
 SELECT 
convert(char(4),[Data creazione pratica],100) mon
,[Cod# prodotto]
FROM dbPratiche 
WHERE Compagnia='GENERTEL'
)       
SELECT MON
,(SELECT COUNT(*) FROM cte t2 WHERE t1.mon=t2.mon and t2.cod#prodotto='qdb') qdbcount
,(SELECT COUNT(*) FROM cte t2 WHERE t1.mon=t2.mon and t2.cod#prodotto='ministeriacle')ministeriaclecount
,COUNT(*) total
FROM cte t1
GROUP BY MON

Upvotes: 1

Related Questions