Pivot query with row and column totals

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Pivot query with row and column totals

Author

Topic

ntswakisto
Starting Member

33 Posts

Posted - 2009-07-01 : 04:24:31

Can anyone help me with this..

I need to add row and column total to my pivot query..
here's the query :

Select PackingSlipDate, [Bag],[BoxAA],[EmptyStand],[FullStand],[Other]

From

(

SELECT HHCT.ContainerType,convert(varchar(10),JMPSH.CreateDate,120) as PackingSlipDate,Count(JMPSH.pkPackingSlipID) AS Total

FROM Cetus.dbo.JMPackSlipHeader as JMPSH

INNER JOIN Cetus.dbo.HHContainerType as HHCT ON JMPSH.fkContainerType = HHCT.pkContainerTypeID

INNER JOIN usCustomers.dbo.Customers As C ON JMPSH.CustomerID = C.CustomerID

Where JMPSH.CreateDate between '2009-05-01' and '2009-05-31'

and C.MerchPreference = 'Y'

GROUP BY HHCT.ContainerType,convert(varchar(10),JMPSH.CreateDate,120)

) AS S

PIVOT

(
Sum(Total)
FOR ContainerType IN ([Bag],[BoxAA],EmptyStand],[FullStand],[Other]
) AS P
ORDER BY PackingSlipDate

Results:
Packaging Date Bag BoxAA EmptyStand FullStand Other
2009/05/04 4 38 8 4 NULL
2009/05/05 17 175 10 NULL NULL
2009/05/06 15 442 4 16 NULL
2009/05/07 6 561 10 5 NULL
2009/05/08 8 372 12 NULL NULL
2009/05/11 4 142 3 1 NULL
2009/05/12 16 151 21 11 NULL

I need results to look like this:

Packaging Date Bag BoxAA EmptyStand FullStand Other Total
2009/05/04 4 38 8 4 NULL 54
2009/05/05 17 175 10 NULL NULL 202
2009/05/06 15 442 4 16 NULL 477
2009/05/07 6 561 10 5 NULL 582
2009/05/08 8 372 12 NULL NULL 392
2009/05/11 4 142 3 1 NULL 150
2009/05/12 16 151 21 11 NULL 199
Total 70 1881 68 37 0 4112

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-01 : 05:29:55

[code]
Select PackingSlipDate, [Bag],[BoxAA],[EmptyStand],[FullStand],[Other],
ISNULL([Bag],0)+ISNULL([BoxAA],0)+ISNULL([EmptyStand],0)+ISNULL([FullStand],0)+ISNULL([Other],0) AS total
From
(
SELECT HHCT.ContainerType,convert(varchar(10),JMPSH.CreateDate,120) as PackingSlipDate,Count(JMPSH.pkPackingSlipID) AS Total
FROM Cetus.dbo.JMPackSlipHeader as JMPSH
INNER JOIN Cetus.dbo.HHContainerType as HHCT ON JMPSH.fkContainerType = HHCT.pkContainerTypeID
INNER JOIN usCustomers.dbo.Customers As C ON JMPSH.CustomerID = C.CustomerID
Where JMPSH.CreateDate between '2009-05-01' and '2009-05-31'
and C.MerchPreference = 'Y'
GROUP BY HHCT.ContainerType,convert(varchar(10),JMPSH.CreateDate,120)
) AS S
PIVOT
(
Sum(Total)
FOR ContainerType IN ([Bag],[BoxAA],EmptyStand],[FullStand],[Other]
) AS P
ORDER BY PackingSlipDate
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-01 : 12:12:17

Also


SELECT DATEADD(dd,DATEDIFF(dd,0,JMPSH.CreateDate),0) as PackingSlipDate,
Count(CASE WHEN HHCT.ContainerType = 'Bag' THEN JMPSH.pkPackingSlipID  ELSE NULL END) AS Bag,
Count(CASE WHEN HHCT.ContainerType = 'BoxAA' THEN JMPSH.pkPackingSlipID  ELSE NULL END) AS BoxAA,
Count(CASE WHEN HHCT.ContainerType = 'EmptyStand' THEN JMPSH.pkPackingSlipID  ELSE NULL END) AS EmptyStand,
Count(CASE WHEN HHCT.ContainerType = 'FullStand' THEN JMPSH.pkPackingSlipID  ELSE NULL END) AS FullStand,
Count(CASE WHEN HHCT.ContainerType = 'Other' THEN JMPSH.pkPackingSlipID  ELSE NULL END) AS Other,
Count(JMPSH.pkPackingSlipID) AS Total
FROM Cetus.dbo.JMPackSlipHeader as JMPSH
INNER JOIN Cetus.dbo.HHContainerType as HHCT ON JMPSH.fkContainerType = HHCT.pkContainerTypeID
INNER JOIN usCustomers.dbo.Customers As C ON JMPSH.CustomerID = C.CustomerID
Where JMPSH.CreateDate between '2009-05-01' and '2009-05-31'
and C.MerchPreference = 'Y'
GROUP BY DATEADD(dd,DATEDIFF(dd,0,JMPSH.CreateDate),0)
ORDER BY PackingSlipDate

ntswakisto
Starting Member

33 Posts

Posted - 2009-07-10 : 07:16:46

quote:

Originally posted by bklr


Select PackingSlipDate, [Bag],[BoxAA],[EmptyStand],[FullStand],[Other],
ISNULL([Bag],0)+ISNULL([BoxAA],0)+ISNULL([EmptyStand],0)+ISNULL([FullStand],0)+ISNULL([Other],0) AS total
From 
(
SELECT HHCT.ContainerType,convert(varchar(10),JMPSH.CreateDate,120) as PackingSlipDate,Count(JMPSH.pkPackingSlipID) AS Total
FROM Cetus.dbo.JMPackSlipHeader as JMPSH
INNER JOIN Cetus.dbo.HHContainerType as HHCT ON JMPSH.fkContainerType = HHCT.pkContainerTypeID
INNER JOIN usCustomers.dbo.Customers As C ON JMPSH.CustomerID = C.CustomerID
Where JMPSH.CreateDate between '2009-05-01' and '2009-05-31'
and C.MerchPreference = 'Y'
GROUP BY HHCT.ContainerType,convert(varchar(10),JMPSH.CreateDate,120)
) AS S
PIVOT
(
Sum(Total)
FOR ContainerType IN ([Bag],[BoxAA],EmptyStand],[FullStand],[Other]
) AS P
ORDER BY PackingSlipDate

thanks i got totals for rows....

I need to get total for columns...help please....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-10 : 11:30:00

quote:

Originally posted by ntswakisto

quote:

Originally posted by bklr


Select PackingSlipDate, [Bag],[BoxAA],[EmptyStand],[FullStand],[Other],
ISNULL([Bag],0)+ISNULL([BoxAA],0)+ISNULL([EmptyStand],0)+ISNULL([FullStand],0)+ISNULL([Other],0) AS total
From 
(
SELECT HHCT.ContainerType,convert(varchar(10),JMPSH.CreateDate,120) as PackingSlipDate,Count(JMPSH.pkPackingSlipID) AS Total
FROM Cetus.dbo.JMPackSlipHeader as JMPSH
INNER JOIN Cetus.dbo.HHContainerType as HHCT ON JMPSH.fkContainerType = HHCT.pkContainerTypeID
INNER JOIN usCustomers.dbo.Customers As C ON JMPSH.CustomerID = C.CustomerID
Where JMPSH.CreateDate between '2009-05-01' and '2009-05-31'
and C.MerchPreference = 'Y'
GROUP BY HHCT.ContainerType,convert(varchar(10),JMPSH.CreateDate,120)
) AS S
PIVOT
(
Sum(Total)
FOR ContainerType IN ([Bag],[BoxAA],EmptyStand],[FullStand],[Other]
) AS P
ORDER BY PackingSlipDate

thanks i got totals for rows....

I need to get total for columns...help please....

use my suggestion then

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources