pivot query
-
hi all,
i am new to t-sql i need to sum of the sales based on months and quarters together can some one help me to solve this.
CREATE TABLE [dbo].[tablesales2](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[cost] [numeric](18, 0) NULL,
[saledate] [datetime] NULL,
)
insert into [tablesales2](name,cost,saledate)values('pen',100,'01/01/2001')
insert into [tablesales2](name,cost,saledate)values('pencil',100,'01/01/2001')
insert into [tablesales2](name,cost,saledate)values('pen',200,'02/02/2001')
insert into [tablesales2](name,cost,saledate)values('pencil',200,'02/02/2001')
insert into [tablesales2](name,cost,saledate)values('pen',300,'04/04/2001')
insert into [tablesales2](name,cost,saledate)values('pencil',300,'04/04/2001')
insert into [tablesales2](name,cost,saledate)values('pen',400,'12/12/2001')
insert into [tablesales2](name,cost,saledate)values('pencil',400,'12/12/2001')
i need to have the output
Name Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Q1 Q2 Q3 Q4 Total
pen 100 200 0 300 0 0 0 0 0 0 0 400 300 300 400 1000
pencil 100 200 0 300 0 0 0 0 0 0 0 400 300 300 400 1000
regards,
-
Syed Khalid, tel me, honestly, it is an interview question or home-work question , isn't it?
-
hi,
neither interview nor homework it is just a requirement.
-
SELECT Name,
SUM(CASE WHEN DATEPART(month,saledate)=1 THEN cost ELSE 0 END) AS Jan,
SUM(CASE WHEN DATEPART(month,saledate)=2 THEN cost ELSE 0 END) AS Feb,
SUM(CASE WHEN DATEPART(month,saledate)=3 THEN cost ELSE 0 END) AS Mar,
SUM(CASE WHEN DATEPART(month,saledate)=4 THEN cost ELSE 0 END) AS Apr,
SUM(CASE WHEN DATEPART(month,saledate)=5 THEN cost ELSE 0 END) AS May,
SUM(CASE WHEN DATEPART(month,saledate)=6 THEN cost ELSE 0 END) AS Jun,
SUM(CASE WHEN DATEPART(month,saledate)=7 THEN cost ELSE 0 END) AS Jul,
SUM(CASE WHEN DATEPART(month,saledate)=8 THEN cost ELSE 0 END) AS Aug,
SUM(CASE WHEN DATEPART(month,saledate)=9 THEN cost ELSE 0 END) AS Sep,
SUM(CASE WHEN DATEPART(month,saledate)=10 THEN cost ELSE 0 END) AS Oct,
SUM(CASE WHEN DATEPART(month,saledate)=11 THEN cost ELSE 0 END) AS Nov,
SUM(CASE WHEN DATEPART(month,saledate)=12 THEN cost ELSE 0 END) AS Dec,
SUM(CASE WHEN DATEPART(quarter,saledate)=1 THEN cost ELSE 0 END) AS Q1,
SUM(CASE WHEN DATEPART(quarter,saledate)=2 THEN cost ELSE 0 END) AS Q2,
SUM(CASE WHEN DATEPART(quarter,saledate)=3 THEN cost ELSE 0 END) AS Q3,
SUM(CASE WHEN DATEPART(quarter,saledate)=4 THEN cost ELSE 0 END) AS Q4,
SUM(cost) AS Total
FROM dbo.tablesales2
GROUP BY Name
ORDER BY Name;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Hmmm.. try this:
; with cte as
(
SELECT name,cost , saledate ,
Quarter_Num = DATEPART(M ,[saledate] )
,Month_Name = LEFT ( DATENAME( M , [saledate]) , 3 )
FROM [dbo].[tablesales2]
)
SELECT
Name,
SUM ( CASE WHEN Month_Name = 'Jan' THEN Cost ELSE 0 END ) AS 'JAN' ,
SUM ( CASE WHEN Month_Name = 'Feb' THEN Cost ELSE 0 END ) AS 'FEB' ,
SUM ( CASE WHEN Month_Name = 'Mar' THEN Cost ELSE 0 END ) AS 'MAR' ,
SUM ( CASE WHEN Month_Name = 'Apr' THEN Cost ELSE 0 END ) AS 'APR' ,
SUM ( CASE WHEN Month_Name = 'May' THEN Cost ELSE 0 END ) AS 'MAY' ,
SUM ( CASE WHEN Month_Name = 'Jun' THEN Cost ELSE 0 END ) AS 'JUN' ,
SUM ( CASE WHEN Month_Name = 'Jul' THEN Cost ELSE 0 END ) AS 'JUL' ,
SUM ( CASE WHEN Month_Name = 'Aug' THEN Cost ELSE 0 END ) AS 'AUG' ,
SUM ( CASE WHEN Month_Name = 'Sep' THEN Cost ELSE 0 END ) AS 'SEP' ,
SUM ( CASE WHEN Month_Name = 'Oct' THEN Cost ELSE 0 END ) AS 'OCT' ,
SUM ( CASE WHEN Month_Name = 'Nov' THEN Cost ELSE 0 END ) AS 'NOV' ,
SUM ( CASE WHEN Month_Name = 'Dec' THEN Cost ELSE 0 END ) AS 'DEC' ,
SUM ( CASE WHEN Quarter_Num BETWEEN 1 AND 3 THEN Cost ELSE 0 END ) AS 'Q1' ,
SUM ( CASE WHEN Quarter_Num BETWEEN 4 AND 6 THEN Cost ELSE 0 END ) AS 'Q2' ,
SUM ( CASE WHEN Quarter_Num BETWEEN 7 AND 9 THEN Cost ELSE 0 END ) AS 'Q3' ,
SUM ( CASE WHEN Quarter_Num BETWEEN 10 AND 12 THEN Cost ELSE 0 END ) AS 'Q4',
SUM ( Cost ) AS Total
FROM
cte
GROUP BY
Name
-
OOooch :pinch:, Mark beat me to it..
one variation between Mark and mine, i used DATENAME for the Month, he used DATEPART.. both do the same, infact, mark's is good one, considering the fact that we don't need that extra LEFT(,3) thingy 😉
-
thanks a lot both of you for your support , both solution works.
-
one thing more is it possible to add total also at the bottom for each months and quarters , i tried to use union didn't work , do i need to add the results to temporary table then i have to aggregate the columns?
-
Try this:
Tweaked with the idea from Mark:
; with cte0 as
(
SELECT name,cost , saledate
,Quarter_Num = DATEPART(Q ,[saledate] )
,Month_Num = DATEPART(M ,[saledate] )
FROM [dbo].[tablesales2]
) -- SELECT * FROM cte0
, cte1 as
(
SELECT
Name,
SUM ( CASE WHEN Month_Num = 1 THEN Cost ELSE 0 END ) AS 'JAN' ,
SUM ( CASE WHEN Month_Num = 2 THEN Cost ELSE 0 END ) AS 'FEB' ,
SUM ( CASE WHEN Month_Num = 3 THEN Cost ELSE 0 END ) AS 'MAR' ,
SUM ( CASE WHEN Month_Num = 4 THEN Cost ELSE 0 END ) AS 'APR' ,
SUM ( CASE WHEN Month_Num = 5 THEN Cost ELSE 0 END ) AS 'MAY' ,
SUM ( CASE WHEN Month_Num = 6 THEN Cost ELSE 0 END ) AS 'JUN' ,
SUM ( CASE WHEN Month_Num = 7 THEN Cost ELSE 0 END ) AS 'JUL' ,
SUM ( CASE WHEN Month_Num = 8 THEN Cost ELSE 0 END ) AS 'AUG' ,
SUM ( CASE WHEN Month_Num = 9 THEN Cost ELSE 0 END ) AS 'SEP' ,
SUM ( CASE WHEN Month_Num = 10 THEN Cost ELSE 0 END ) AS 'OCT' ,
SUM ( CASE WHEN Month_Num = 11 THEN Cost ELSE 0 END ) AS 'NOV' ,
SUM ( CASE WHEN Month_Num = 12 THEN Cost ELSE 0 END ) AS 'DEC' ,
SUM ( CASE WHEN Quarter_Num = 1 THEN Cost ELSE 0 END ) AS 'Q1' ,
SUM ( CASE WHEN Quarter_Num = 2 THEN Cost ELSE 0 END ) AS 'Q2' ,
SUM ( CASE WHEN Quarter_Num = 3 THEN Cost ELSE 0 END ) AS 'Q3' ,
SUM ( CASE WHEN Quarter_Num = 4 THEN Cost ELSE 0 END ) AS 'Q4',
SUM ( Cost ) AS Total
FROM
cte0
GROUP BY
Name
)
SELECT Name
,JAN ,FEB ,MAR
,APR ,MAY ,JUN
,JUL ,AUG ,SEP
,OCT ,NOV ,DEC
,Q1, Q2, Q3, Q4
,Total
FROM
cte1
UNION ALL
SELECT 'Grand Total'
, SUM (JAN ), SUM (FEB ), SUM (MAR )
, SUM (APR ), SUM (MAY ), SUM (JUN )
, SUM (JUL ), SUM (AUG ), SUM (SEP )
, SUM (OCT ), SUM (NOV ), SUM (DEC)
, SUM ( Q1 ), SUM ( Q2 ), SUM ( Q3 ), SUM ( Q4 )
, SUM (Total)
FROM cte1
-
wow thanks alot ... now i am learning the power of cte...
-
sayedkhalid99 (9/22/2010)
one thing more is it possible to add total also at the bottom for each months and quarters , i tried to use union didn't work , do i need to add the results to temporary table then i have to aggregate the columns?It's probably most efficient to use the WITH ROLLUP clause, but I'm not at a computer where I can test it.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
I finally had a chance to test this on a table with 18K records. Here are the results for producing a grand total:
WITH ROLLUP
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'LEAD'. Scan count 1, logical reads 2428, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 93 ms, elapsed time = 108 ms.
SQL Server parse and compile time:
CPU time = 63 ms, elapsed time = 63 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.
UNION ALL
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'LEAD'. Scan count 2, logical reads 4856, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 187 ms, elapsed time = 209 ms.
As you can see, the UNION ALL has exactly double the number of Scan Counts and Logical Reads of the WITH ROLLUP.
Here is the query I used with the rollup.
SELECT CASE Grouping(Name) WHEN 1 THEN 'Total' ELSE Name END AS [Name],
SUM(CASE WHEN DATEPART(month,saledate)=1 THEN cost ELSE 0 END) AS Jan,
SUM(CASE WHEN DATEPART(month,saledate)=2 THEN cost ELSE 0 END) AS Feb,
SUM(CASE WHEN DATEPART(month,saledate)=3 THEN cost ELSE 0 END) AS Mar,
SUM(CASE WHEN DATEPART(month,saledate)=4 THEN cost ELSE 0 END) AS Apr,
SUM(CASE WHEN DATEPART(month,saledate)=5 THEN cost ELSE 0 END) AS May,
SUM(CASE WHEN DATEPART(month,saledate)=6 THEN cost ELSE 0 END) AS Jun,
SUM(CASE WHEN DATEPART(month,saledate)=7 THEN cost ELSE 0 END) AS Jul,
SUM(CASE WHEN DATEPART(month,saledate)=8 THEN cost ELSE 0 END) AS Aug,
SUM(CASE WHEN DATEPART(month,saledate)=9 THEN cost ELSE 0 END) AS Sep,
SUM(CASE WHEN DATEPART(month,saledate)=10 THEN cost ELSE 0 END) AS Oct,
SUM(CASE WHEN DATEPART(month,saledate)=11 THEN cost ELSE 0 END) AS Nov,
SUM(CASE WHEN DATEPART(month,saledate)=12 THEN cost ELSE 0 END) AS Dec,
SUM(CASE WHEN DATEPART(quarter,saledate)=1 THEN cost ELSE 0 END) AS Q1,
SUM(CASE WHEN DATEPART(quarter,saledate)=2 THEN cost ELSE 0 END) AS Q2,
SUM(CASE WHEN DATEPART(quarter,saledate)=3 THEN cost ELSE 0 END) AS Q3,
SUM(CASE WHEN DATEPART(quarter,saledate)=4 THEN cost ELSE 0 END) AS Q4,
SUM(cost) AS Total
FROM tablesales2
GROUP BY Name
WITH ROLLUP
ORDER BY Grouping(Name), Name;
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply