PVIOT Vs CASE for summary report

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

SQL Server 2008 Forums

Transact-SQL (2008)

PVIOT Vs CASE for summary report

Author

Topic

samalkobi
Starting Member

2 Posts

Posted - 2014-12-24 : 11:24:48

Hey all,
I'm trying to figure out how to write this query so that i can get both an average and the total count for some itemcodes PER month.

At the moment i have this simple query that gives me the average and total for the entire year, but i want to have the months in the column and have 2 rows for "Total Count" and "Average price". I was able to use a SUM(CASE WHEN MONTH(date) = 01 THEN (item_price) ELSE 0 END) which gives me the sum for each month, but i can't figure out how to change this to give me a Count and an Average.

This is for a 3 part report where i'm using sub-reports in SSRS.

I'm getting the data mainly from 1 table Paydetail, and i have 1 join to another table OrderHeader to get the dates.

This is an example of the script i have to get the monthly total costs. The result is 2 rows for A & B, and the columns are the Months.

SELECT
	p.pyt_itemcode,
	SUM(CASE WHEN MONTH(o.ord_completiondate) = 01 THEN pyd_amount ELSE 0 END) AS Jan,
	SUM(CASE WHEN MONTH(o.ord_completiondate) = 02 THEN pyd_amount ELSE 0 END) AS Feb,
	SUM(CASE WHEN MONTH(o.ord_completiondate) = 03 THEN pyd_amount ELSE 0 END) AS Mar,
	SUM(CASE WHEN MONTH(o.ord_completiondate) = 04 THEN pyd_amount ELSE 0 END) AS Apr,
	SUM(CASE WHEN MONTH(o.ord_completiondate) = 05 THEN pyd_amount ELSE 0 END) AS May,
	SUM(CASE WHEN MONTH(o.ord_completiondate) = 06 THEN pyd_amount ELSE 0 END) AS Jun,
	SUM(CASE WHEN MONTH(o.ord_completiondate) = 07 THEN pyd_amount ELSE 0 END) AS Jul,
	SUM(CASE WHEN MONTH(o.ord_completiondate) = 08 THEN pyd_amount ELSE 0 END) AS Aug,
	SUM(CASE WHEN MONTH(o.ord_completiondate) = 09 THEN pyd_amount ELSE 0 END) AS Sep,
	SUM(CASE WHEN MONTH(o.ord_completiondate) = 10 THEN pyd_amount ELSE 0 END) AS Oct,
	SUM(CASE WHEN MONTH(o.ord_completiondate) = 11 THEN pyd_amount ELSE 0 END) AS Nov,
	SUM(CASE WHEN MONTH(o.ord_completiondate) = 12 THEN pyd_amount ELSE 0 END) AS Dec
from Paydetail p
join orderheader o on p.mov_number=o.mov_number
where p.pyt_itemcode in ('COMDIS','COMFEE')
AND o.ord_completiondate >='2014-01-01T00:00:00.000'
AND o.ord_completiondate <= '2014-12-31T23:59:59.999'
GROUP BY P.PYT_ITEMCODE

I also got suggestions from stackoverflow to use either GROUPING SET, ROLLUP, and even TempTables (I'd rather not use temp tables if possible)

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-24 : 11:31:28

Just add more columns to the select:

SUM(CASE WHEN MONTH(o.ord_completiondate) = 01 THEN pyd_amount ELSE 0 END) /
COUNT(CASE WHEN MONTH(o.ord_completiondate) = 01 THEN 1 END) AS Jan_avg,
COUNT(CASE WHEN MONTH(o.ord_completiondate) = 01 THEN 1 END) AS Jan_ct,

Note that COUNT doesn't count nulls. You may need some extra logic to avoid divide by 0 errors. But I think you get the idea

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources