Help... Group by date - 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

Help... Group by date

Author

Topic

coolrefche
Starting Member

4 Posts

Posted - 2008-07-15 : 03:45:14

Hi,

My brain has completely frozen... I need some help.

I have written a basic query to extract some details from a database:

select datecreated, count(*) as "No of Items"
from Sale_Items (nolock)
where customerid = 2
group by datecreated order by datecreated desc
Date/Time Created, No Items
2005-10-26 14:31:27.000, 1
2005-10-20 16:57:18.000, 1
2005-10-20 16:55:15.000, 1
2005-10-20 16:53:49.000, 1
2005-10-20 16:53:19.000, 1
2005-10-20 16:52:38.000, 1
2005-10-20 16:51:59.000, 1

I want to group these results by date, get a sum of how many items are sold per day.

I would appreciate any help...

Regards

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-15 : 03:48:46

[code]SELECT DATEADD(DAY, DATEDIFF(DAY, 0, datecreated), 0), COUNT(*) AS "No of Items"
FROM Sale_Items (NOLOCK)
WHERE customerid = 2
GROUP BY DATEADD(DAY, DATEDIFF(DAY, 0, datecreated), 0) ORDER BY datecreated DESC[/code]

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-15 : 03:49:05

[code]DECLARE @Sample TABLE (Created DATETIME, Items INT)

INSERT @Sample
SELECT '2005-10-26 14:31:27.000', 1 UNION ALL
SELECT '2005-10-20 16:57:18.000', 1 UNION ALL
SELECT '2005-10-20 16:55:15.000', 1 UNION ALL
SELECT '2005-10-20 16:53:49.000', 1 UNION ALL
SELECT '2005-10-20 16:53:19.000', 1 UNION ALL
SELECT '2005-10-20 16:52:38.000', 1 UNION ALL
SELECT '2005-10-20 16:51:59.000', 1

SELECT DATEADD(DAY, DATEDIFF(DAY, '19000101', Created), '19000101') AS Created,
SUM(Items) AS Items
FROM @Sample
GROUP BY DATEDIFF(DAY, '19000101', Created)[/code]

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-15 : 03:49:53

select dateadd(day,datediff(day,0,datecreated),0) as datecreated, count(*) as "No of Items"
from Sale_Items (nolock)
where customerid = 2
group by dateadd(day,datediff(day,0,datecreated),0) order by dateadd(day,datediff(day,0,datecreated),0) desc

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-15 : 03:50:41