June 15, 2010 at 5:58 am
Hello All
I have a table which consists many records, which also have an additional flag
The data looks something like this
Postcode Flag
AB Y
AB
AB
AB Y
BL
BL Y
BL Y
What I want to do is query this table and return the following:
Postcode recordcount flagcount
AB 4 2
BL 3 2
Now at the moment I am getting these results by running 2 seperate queries, which I want to be able to perform in just 1 query. I have never done a sub query before so would like advise on how to build this statement
Thanks guys
June 15, 2010 at 6:14 am
You can use a case statement inside sum function to check if you need to count a record according to the value in the flag column. Here is an example:
use tempdb
go
create table Demo (PostCode char(2), flag char(1) null)
go
--Inserting the data
insert into Demo (PostCode, flag) values ('AB', null)
go 2
insert into Demo (PostCode, flag) values ('AB', 'Y')
go 2
insert into Demo (PostCode, flag) values ('BL', 'Y')
go 2
insert into Demo (PostCode, flag) values ('BL', NULL)
go
--First way. Using case to decide if the record should be counted
select PostCode, count(*) as NumOfRecords,
sum(CASE WHEN flag = 'Y' THEN 1 ELSE 0 END) as NumOfFlag
from Demo
group by PostCode
go
--Cleanup
drop table Demo
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 15, 2010 at 6:18 am
Ah, i was about to post the same code! 🙂
June 15, 2010 at 6:53 am
Works a treat, thanks very much! This will save me lots of time...
🙂
June 23, 2010 at 6:48 am
You can also use the following query....
select PostCode, count(*) as NumOfRecords,
count(flag) as NumOfFlag
from Demo
group by PostCode
Prashant Bhatt
Sr Engineer - Application Programming
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply