Counting instances of a value in a field

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)

Counting instances of a value in a field

Author

Topic

rosmiq
Starting Member

5 Posts

Posted - 2010-08-26 : 07:44:18

I am joining 2 tables using the FULL OUTER JOIN. For each job ref you may get multiple lines of equipment used. The equipment field will list all the types of equipment, i.e:

JobRef Equipment

12345 Type1
12345 Type2
12345 Type2
23456 Type1

This will display on multiple lines. I need to be able to count how many types of equipment for each job. I want it to be displayed like the following:

JobRef Count of Type1 Count of Type2
12345 1 2
23456 1 0

I am using reporting services SSRS2005 and would like to know how I need to do this.

Can anybody please help?

Many thanks

rosmiq

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-08-26 : 09:48:33

SELECT
JobRef
,SUM(CASE WHEN Equipment = 'Type1' THEN 1 ELSE 0 END) as Type1
,SUM(CASE WHEN Equipment = 'Type2' THEN 1 ELSE 0 END) as Type2

Jim

Everyday I learn something that somebody else already knew

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-26 : 09:49:59

quote:

Originally posted by jimf

SELECT 
     JobRef
    ,SUM(CASE WHEN Equipment = 'Type1' THEN 1 ELSE 0 END) as Type1
    ,SUM(CASE WHEN Equipment = 'Type2' THEN 1 ELSE 0 END) as Type2
FROM YourTableNameHerePlease
GROUP BY JobRef

N 56°04'39.26"
E 12°55'05.63"

rosmiq
Starting Member

5 Posts

Posted - 2010-08-26 : 10:30:10

Thank you for your replies, it worked a treat!!!! :-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources