SQL sub query?

Question

Post reply

SQL sub query?

bicky1980

SSCrazy

Points: 2079
More actions
June 15, 2010 at 5:58 am

#225612

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

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/