How to get count per 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

SQL Server 2005 Forums

Transact-SQL (2005)

How to get count per day

Author

Topic

mrsaif
Starting Member

43 Posts

Posted - 2008-04-09 : 06:38:00

I have a table hits with following fields with sample Data
Code: CreatedOn:
4 2008-04-01 14:48:15.327
4 2008-04-01 13:53:41.923
4 2008-04-02 15:16:25.327
5 2008-04-02 16:31:07.383
5 2008-04-02 16:25:07.383
5 2008-04-02 16:28:07.383
5 2008-04-03 16:50:34.217
4 2008-04-03 13:23:30.767
3 2008-04-04 13:41:41.627
5 2008-04-04 13:55:00.497
__________________________________________________________
i want to create a query to get count of the code per day.
__________________________________________________________
Code: Date: Count:
3 2008-04-04 1
4 2008-04-01 2
4 2008-04-02 1
4 2008-04-03 1
5 2008-04-02 3
5 2008-04-03 1
------------------------------------------------------------
How can i do this to count code per day in a single query?
I am using sql server 2005.
==============================================================

Muhammad Saifullah

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-09 : 06:44:48

[code]Select Dateadd(day, Datediff(day, 0, CreatedOn), 0) as CreatedOn, Count(Code) as [Count]
from table
group by Dateadd(day, Datediff(day, 0, CreatedOn), 0)
order by 1[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-09 : 06:47:14

[code]DECLARE @Sample TABLE (Code TINYINT, CreatedOn DATETIME)

INSERT @Sample
SELECT 4, '2008-04-01 14:48:15.327' UNION ALL
SELECT 4, '2008-04-01 13:53:41.923' UNION ALL
SELECT 4, '2008-04-02 15:16:25.327' UNION ALL
SELECT 5, '2008-04-02 16:31:07.383' UNION ALL
SELECT 5, '2008-04-02 16:25:07.383' UNION ALL
SELECT 5, '2008-04-02 16:28:07.383' UNION ALL
SELECT 5, '2008-04-03 16:50:34.217' UNION ALL
SELECT 4, '2008-04-03 13:23:30.767' UNION ALL
SELECT 3, '2008-04-04 13:41:41.627' UNION ALL
SELECT 5, '2008-04-04 13:55:00.497'

SELECT Code,
DATEADD(DAY, DATEDIFF(DAY, '19000101', CreatedOn), '19000101') AS Date,
COUNT(*) AS [Count]
FROM @Sample
GROUP BY Code,
DATEDIFF(DAY, '19000101', CreatedOn)
ORDER BY Code,
DATEDIFF(DAY, '19000101', CreatedOn[/code]

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

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-04-09 : 06:48:44

TRY THIS

SELECT Code, DATEADD(DAY, DATEDIFF(DAY, 0, CreatedeOn), 0),
COUNT(DATEADD(DAY, DATEDIFF(DAY, 0, CreatedeOn), 0)) 'Cnt'
FROM (
SELECT 4 Code, '2008-04-01 14:48:15.327' CreatedeOn UNION ALL
SELECT 4, '2008-04-01 13:53:41.923' UNION ALL
SELECT 4, '2008-04-02 15:16:25.327' UNION ALL
SELECT 5, '2008-04-02 16:31:07.383' UNION ALL
SELECT 5, '2008-04-02 16:25:07.383' UNION ALL
SELECT 5, '2008-04-02 16:28:07.383' UNION ALL
SELECT 5, '2008-04-03 16:50:34.217' UNION ALL
SELECT 4, '2008-04-03 13:23:30.767' UNION ALL
SELECT 3, '2008-04-04 13:41:41.627' UNION ALL
SELECT 5, '2008-04-04 13:55:00.497') A
GROUP BY Code, DATEADD(DAY, DATEDIFF(DAY, 0, CreatedeOn), 0)

mrsaif
Starting Member

43 Posts

Posted - 2008-04-09 : 07:29:53

Thank you Harsh Athalye,
your reply solved my problem :)

Muhammad Saifullah

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-09 : 07:35:33

Really?
Where is the Code column?

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

somalia
Starting Member

10 Posts

Posted - 2009-09-29 : 06:49:54

i have a similar problem.
there is table where datas are getting saved as
agentname saledate appno
===============================
ph0003 09/28/2009 A0010
ph0003 09/28/2009 A0011
ph0004 09/28/2009 A0012
ph0004 09/28/2009 A0013
ph0005 09/28/2009 A0014
ph0005 09/28/2009 A0015
ph0003 09/27/2009 A0002
ph0003 09/27/2009 A0003
ph0004 09/27/2009 A0004
ph0004 09/27/2009 A0005
ph0005 09/27/2009 A0006
ph0005 09/27/2009 A0007

the output should be

Agentname 09/27/2009 09/28/2009 Total
============================================
ph0003 2 2 4
ph0004 2 2 4

Can anybody tell me how to do this?????

somalia

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-29 : 06:54:27

quote:
Originally posted by somalia

i have a similar problem.
there is table where datas are getting saved as
agentname saledate appno
===============================
ph0003 09/28/2009 A0010
ph0003 09/28/2009 A0011
ph0004 09/28/2009 A0012
ph0004 09/28/2009 A0013
ph0005 09/28/2009 A0014
ph0005 09/28/2009 A0015
ph0003 09/27/2009 A0002
ph0003 09/27/2009 A0003
ph0004 09/27/2009 A0004
ph0004 09/27/2009 A0005
ph0005 09/27/2009 A0006
ph0005 09/27/2009 A0007

the output should be

Agentname 09/27/2009 09/28/2009 Total
============================================
ph0003 2 2 4
ph0004 2 2 4

Can anybody tell me how to do this?????

somalia

Answered here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=133596

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 06:58:40

dont hijack threads. post as a new question to get quick solutions in future. you can do like this to get o/p

SELECT Agentname,
COUNT(CASE WHEN saledate='09/27/2009' THEN appno ELSE NULL END) AS [09/27/2009],
COUNT(CASE WHEN saledate='09/28/2009' THEN appno ELSE NULL END) AS [09/28/2009],
COUNT(appno) AS Total 
FROM table
GROUP BY Agentname

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources