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.
| Author |
Topic |
|
ajreynolds
Starting Member
9 Posts |
 Posted - 2009-09-02 : 14:13:37
|
| This is probably a pretty basic question, but I'm having trouble figuring out how to do the following. I have a table with a date field and a flag. I want to do a single SQL statement that returns the total number of records for each date in a range as well as the number of records with the flag set to 1. I could do two statements as follows:select my_date,count(*) from my_table where my_date between '20090826' and '20090902' group by my_date order by my_dateselect my_date,count(*) from my_table where my_date between '20090826' and '20090902' and my_flag=1 group by my_date order by my_dateWhat I want is a single statement that does something like select my_date,count(*),count(but only if my_flag=1) from my_table where my_date between '20090826' and '20090902' group by my_date order by my_dateI could make the second count in this statement another select, but that would be pretty slow.Is this sort of thing possible with a single statement? Thanks,Andy |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-09-02 : 14:19:31
|
You can do that witha CASE statement:select my_date, count(*), count(CASE WHEN my_flag = 1 THEN 1 ELSE NULL END) from my_table where my_date between '20090826' and '20090902' group by my_date order by my_date |
 |
|
|
ajreynolds
Starting Member
9 Posts |
Posted - 2009-09-02 : 14:40:38
|
| Cool, thanks. That's exactly what I needed.Andy |
|
|
|
|
|
|
Conditional counts?