multiple aggregation in sql server

Posted by Klbaiju under Sql Server on 10/15/2013 | Points: 10 | Views : 1418 | Status : [Member] | Replies : 6
Hi all,

I want to display multiple aggregation in sql server
following code is working one

SELECT PRODUCT , FRED, KATE
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product) up
PIVOT (max(QTY) FOR CUST IN (FRED, KATE) ) as p
ORDER BY PRODUCT.
my requirement is to display max(qty) and sum (qty)
like this

SELECT PRODUCT [prod / max sum], FRED, KATE
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product) up
PIVOT (max(QTY) FOR CUST IN (FRED, KATE)+'-'+sum(QTY) FOR CUST IN (FRED, KATE) ) as p
ORDER BY PRODUCT
this is not working.
how to solve this

Regards
Baiju




Responses

Posted by: Bandi on: 10/15/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
--Try this.... Let me know the result
;WITH MaxQty AS
(
SELECT * FROM (SELECT CUST, PRODUCT, QTY , CUST
FROM Product) up
PIVOT (max(QTY) FOR CUST IN ([FRED], [KATE])) as p
), SumQty AS
( SELECT * FROM (
SELECT CUST, PRODUCT, QTY , CUST
FROM Product) up1
PIVOT (sum(QTY) FOR CUST IN ([FRED], [KATE]) ) as p1
)
SELECT C1.PRODUCT, c1.[FRED]-c2.[FRED] AS FRED , c1.[KATE]-c2.[KATE] AS KATE -- if you wish to get the string format Cast each column name to VARCHAR
FROM MaxQty c1
JOIN SumQty c2 ON c1.PRODUCT= c2.PRODUCT


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 10/15/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
For String representation of MaxQty-SumQty,
SELECT C1.PRODUCT

,CAST ( c1.[FRED] AS VARCHAR(10))+ '-' + CAST( c2.[FRED] AS VARCHAR(10)) as FRED
,CAST(c1.[KATE] AS VARCHAR(10))+ '-' + CAST( c2.[KATE] AS VARCHAR(10)) as KATE
.
.
.
.


References for alternate solutions:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/39379dd8-6a3a-4da4-8f7c-580c69d88cf4/using-pivot-with-multiple-aggregates?forum=transactsql
http://stackoverflow.com/questions/15274305/is-it-possible-to-have-multiple-pivots-using-the-same-pivot-column-using-sql-ser
http://www.sqlservercentral.com/Forums/Topic394342-338-1.aspx#bm394757
http://stackoverflow.com/questions/18176138/t-sql-pivot-with-multiple-aggregates-on-the-same-column

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Klbaiju on: 10/15/2013 [Member] Starter | Points: 25

Up
0
Down
Hi bandi,
i got this error
Msg 8156, Level 16, State 1, Line 6
The column 'CUST' was specified multiple times for 'up'.
Msg 8156, Level 16, State 1, Line 11
The column 'CUST' was specified multiple times for 'up1'.


Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 10/15/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
;WITH MaxQty AS
(SELECT * FROM (SELECT CUST, PRODUCT, QTY FROM Product) up PIVOT (max(QTY) FOR CUST IN ([FRED], [KATE])) as p
), SumQty AS( SELECT * FROM (SELECT CUST, PRODUCT, QTY FROM Product) up1 PIVOT (sum(QTY) FOR CUST IN ([FRED], [KATE]) ) as p1)


EDIT: There was a column CUST twice in the SELECT.. that what you got the error.. Use above code which is rectified by me... and add final SELECT statement to the above code

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 10/15/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
;WITH MaxQty AS 
(SELECT *
FROM (SELECT CUST, PRODUCT, QTY FROM Product) up PIVOT (max(QTY) FOR CUST IN ([FRED], [KATE])) as p
), SumQty AS
( SELECT *
FROM (SELECT CUST, PRODUCT, QTY FROM Product) up1 PIVOT (sum(QTY) FOR CUST IN ([FRED], [KATE]) ) as p1
)
SELECT C1.PRODUCT
,CAST ( c1.[FRED] AS VARCHAR(10))+ '-' + CAST( c2.[FRED] AS VARCHAR(10)) as FRED
,CAST(c1.[KATE] AS VARCHAR(10))+ '-' + CAST( c2.[KATE] AS VARCHAR(10)) as KATE
FROM MaxQty c1
JOIN SumQty c2 ON c1.PRODUCT= c2.PRODUCT



Click on "Mark as Answer" if you have resolved/workaround the issue

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 10/22/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
"Mark as Answer " if you solved the issue with the help of above posts...

If NOT , post us back

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response