Summing prices day by day - 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

General SQL Server Forums

New to SQL Server Programming

Summing prices day by day

Author

Topic

vesben
Starting Member

7 Posts

Posted - 2013-10-23 : 09:43:45

Hallo,
Let suppose that we have a table which look like this

BillDate Price
01.01.2013 2.00
01.01.2013 1.00
02.01.2013 3.00
02.01.2013 2.00
03.01.2013 1.00

I would like to sum a prices day by day
and output to be like this

BillDate SumDaylyPrice
01.01.2013 3.00
02.01.2013 5.00
03.01.2013 1.00

To point I’ve reached myself is a query:

SELECT BillDate, (SELECT SUM( Price) FROM Table1 ) AS SumDaylyPrice
FROM Table1
WHERE BillDate BETWEEN
(SELECT Min(BillDate) FROM Table1)
AND
(SELECT Max(BillDate) FROM Table1)
GROUP BY BillDate

but this doesn’t work- summing everityng

I don’t know how to indicate in first row of query
SELECT BillDate, (SELECT SUM( Price) FROM Table1 WHERE DATE = ????) AS SumPrice
a WHERE clause for every day separatly.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-23 : 09:46:49

You shouldn't need to specify the max and min unless you wanted to filter it to a subset

SELECT BillDate, SUM(Price) AS SumDaylyPrice
FROM Table1
GROUP BY BillDate
ORDER BY BillDate;

This assumes that BillDate is of type DATE or DATETIME or SMALLDATETIME, and that you don't have any time portion stored in that column. If you do, change it to this:


SELECT 
	DATEADD(dd,DATEDIFF(dd,0,BillDate),0) AS BillDate, SUM(Price) AS SumDaylyPrice
FROM Table1
GROUP BY DATEADD(dd,DATEDIFF(dd,0,BillDate),0)
ORDER BY DATEADD(dd,DATEDIFF(dd,0,BillDate),0);

vesben
Starting Member

7 Posts

Posted - 2013-10-23 : 11:14:31

quote:
Originally posted by James K

You shouldn't need to specify the max and min unless you wanted to filter it to a subset
SELECT BillDate, SUM(Price) AS SumDaylyPrice
FROM Table1
GROUP BY BillDate
ORDER BY BillDate;
This assumes that BillDate is of type DATE or DATETIME or SMALLDATETIME, and that you don't have any time portion stored in that column. If you do, change it to this:

SELECT
DATEADD(dd,DATEDIFF(dd,0,BillDate),0) AS BillDate, SUM(Price) AS SumDaylyPrice
FROM Table1
GROUP BY DATEADD(dd,DATEDIFF(dd,0,BillDate),0)
ORDER BY DATEADD(dd,DATEDIFF(dd,0,BillDate),0);

Thanks to James K for his fast and competent answer.
I spend some times to understand how DATEADD and DATEDIFF
working and arrange text to ACCESS syntax.
I checked that and it's working fine.
This forum is golden mine for beginners.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources