Percentages grouped by days - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Percentages grouped by days

Author

Topic

elinenbe
Starting Member

3 Posts

Posted - 2010-01-27 : 17:05:01

I have a table that looks like

DateTime(DateTime), Product (varchar),Cost (double)

What I would like to do is create a report that is grouped by day, and then for each day the total amount spent, and the percentage spent on each product for a given day

So, the results would look like:

Day,Total Amount Spent,Product,% Spent on given Product

What I have so far is this...


select 
	(select sum(cost) from buyingdata
						where 	
						DateTime >= '2010-01-21' and 
						DateTime < '2010-01-22' and 
					  ) as 'total spent in 24h', 
		sum(cost) as 'Total spent in 24h for product',
		sum(cost)/(	select sum(cost) from buyingdata
						where 	
						DateTime >= '2010-01-21' and 
						DateTime < '2010-01-22' and 
					  ) * 100 as '% Of Total Spent',
					  
	product
from
	buyingdate
where  
	DateTime >= '2010-01-21' and 
	DateTime < '2010-01-22' and 
group by
	product
order by 
	product

Though this only gives me the results for one day -- I'd like to have this query give the results for each day. Any help is appreciated as I can't figure it out!

Thanks!
Eric

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-28 : 03:09:16

[code]
SELECT DATEADD(dd,DATEDIFF(dd,0,[DateTime]),0),Product,SUM(Cost) AS [Total Amount Spent],
SUM(Cost) * 100.0/SUM(Cost) OVER () AS [% Spent on given Product]
FROM Table
GROUP BY DATEADD(dd,DATEDIFF(dd,0,[DateTime]),0),Product
[/code]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-01-28 : 12:31:27

quote:

Originally posted by visakh16


SELECT DATEADD(dd,DATEDIFF(dd,0,[DateTime]),0),Product,SUM(Cost) AS [Total Amount Spent],
SUM(Cost) * 100.0/SUM(Cost) OVER () AS [% Spent on given Product]
FROM Table
GROUP BY DATEADD(dd,DATEDIFF(dd,0,[DateTime]),0),Product

I didn't think you could use an OVER clause like that with a GROUP BY clause.. Plus you'd need to partiton by Date and Product.?.?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-01-28 : 12:32:17

This "works" as in it runs, but I'm not sure exactly how you wanted things group as you didn't supply any data or expected output:

SELECT
	DATEADD(DAY, DATEDIFF(DAY, 0, BD.[DateTime]), 0),
	SUM(BD.Cost) AS [Total spent in 24h for product],
	T.TotalForDay AS [total spent in 24h],
	BD.Product,
	(SUM(BD.Cost) / T.TotalForDay) * 100.00 AS [% Of Total Spent]
FROM
	BuyingDate AS BD
INNER JOIN
	(
		-- Get the sum for ALL products by day
		-- Then join that
		SELECT
			SUM(Cost) AS TotalForDay,
			DATEADD(DAY, DATEDIFF(DAY, 0, [DateTime]), 0) AS [DateTime]
		FROM
			@BuyingDate
		GROUP BY
			DATEADD(DAY, DATEDIFF(DAY, 0, [DateTime]), 0)
	) AS T
	ON DATEADD(DAY, DATEDIFF(DAY, 0, BD.[DateTime]), 0) = t.[DateTime]
GROUP BY
	DATEADD(DAY, DATEDIFF(DAY, 0, BD.[DateTime]), 0),
	BD.Product,
	T.TotalForDay

elinenbe
Starting Member

3 Posts

Posted - 2010-01-28 : 12:35:32

visakh16 -- still isn't working for me. I'm using SQL 2005, and am getting:

Msg 8120, Level 16, State 1, Line 1
Column 'Table.Cost' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-28 : 12:37:12

Can you show us the exact query that you ran?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-01-28 : 12:43:52

Here is a blob of sample data:

DECLARE @BuyingDate TABLE (Product VARCHAR(10), Cost MONEY, [DateTime] DATETIME)

INSERT @BuyingDate
SELECT 'Car', $500.00, '20090101'
UNION ALL SELECT 'Car', $600.00, '20090101'
UNION ALL SELECT 'Car', $700.00, '20090101'
UNION ALL SELECT 'Car', $800.00, '20090101'
UNION ALL SELECT 'Car', $900.00, '20090101'
UNION ALL SELECT 'Car', $1000.00, '20090101'
UNION ALL SELECT 'Car', $600.00, '20090102'
UNION ALL SELECT 'Car', $700.00, '20090102'
UNION ALL SELECT 'Car', $800.00, '20090102'
UNION ALL SELECT 'Boat', $634.00, '20090101'
UNION ALL SELECT 'Boat', $778.00, '20090101'
UNION ALL SELECT 'Boat', $898.00, '20090101'
UNION ALL SELECT 'Boat', $900.76, '20090101'
UNION ALL SELECT 'Boat', $106.00, '20090101'
UNION ALL SELECT 'Boat', $645.00, '20090102'
UNION ALL SELECT 'Boat', $700.87, '20090102'
UNION ALL SELECT 'Boat', $829.00, '20090102'

elinenbe
Starting Member

3 Posts

Posted - 2010-01-28 : 12:47:28

Lamprey's query worked for me, and I can adjust from there -- just the other query "looks" much slicker. Here's what I ran:


SELECT 
	DATEADD(dd,DATEDIFF(dd,0,[DateTime]),0), 
	Product,
	SUM(Cost) AS [Total Cost],
	SUM(Cost) * 100.0/SUM(Cost) OVER () AS '% Cost on Product'
FROM 
	BuyingData

GROUP BY 
	DATEADD(dd,DATEDIFF(dd,0,[DateTime]),0),
	Product

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources