Getting count per date base! Query problem.

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)

Getting count per date base! Query problem.

Author

Topic

mrsaif
Starting Member

43 Posts

Posted - 2008-04-17 : 06:07:07

My table contains following data
date====isActive(bit)
04/04/2005 12:15:300====0
04/04/2005 12:10:300====0
04/04/2005 12:20:300====0
04/04/2005 12:20:300====1
04/04/2005 12:20:300====1

04/05/2005 12:15:300====0
04/05/2005 12:10:300====0
04/05/2005 12:20:300====0
04/05/2005 12:20:300====0
04/05/2005 12:20:300====1

04/06/2005 12:15:300====0
04/06/2005 12:10:300====1
04/06/2005 12:20:300====0
04/06/2005 12:20:300====1
04/06/2005 12:20:300====0
============================================
I want to count number of Active and Inactive Items per day. How can i do this. Following is the desired result set according to the above data..
DATE====InActive_count(0)====ActiveCount(1)===Total
04/04/2008====3===2===5
04/05/2008====4===1===5
04/06/2008====3===2===5

==========================
I can get count total count per day using following query.
Select Dateadd(day, Datediff(day, 0, date), 0) as date, Count(id) as [Count]
from myTable
group by Dateadd(day, Datediff(day, 0, date), 0)
order by date desc
==========
how does i change this query to get desired result....?

Muhammad Saifullah

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-17 : 06:11:39

SELECT DATEADD(d,DATEDIFF(d,0,date),0),
SUM(CASE WHEN IsActive=0 THEN 1 ELSE 0 END) AS InActiveCount,
SUM(CASE WHEN IsActive=1 THEN 1 ELSE 0 END) AS ActiveCount,
COUNT(*) AS Total
GROUP BY DATEADD(d,DATEDIFF(d,0,date),0)

mrsaif
Starting Member

43 Posts

Posted - 2008-04-17 : 06:20:39

Thanks visakh murukes,
:)

Muhammad Saifullah

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources