Help With getting a count query to work

Question

Post reply

Help With getting a count query to work

ppierce10

SSC Enthusiast

Points: 107
More actions
November 25, 2009 at 6:28 am

#137153

I am trying to create a query that will count the number of incidents in a table. When I create these two separate queries I get the results back, but they are returned as two separate results
SELECT COUNT (tblUseOfForce.Taser) AS Taser
FROM tblUseOfForce INNER JOIN
tblIncident ON tblUseOfForce.EventNumber = tblIncident.EventNumber
Where (Year(DateReceived) = 2008) AND tblUseOfForce.taser = 1
SELECT COUNT (tblUseOfForce.PhysicalForce) AS PhysicalForce
FROM tblUseOfForce INNER JOIN
tblIncident ON tblUseOfForce.EventNumber = tblIncident.EventNumber
Where (Year(DateReceived) = 2008) AND tblUseOfForce.PhysicalForce = 1
But I want to combine the two into on results set. I have tried several ways but none seem to work. Can anyone help and/or explain what the proper way to do this is. I have been reading on the use of sub-queries and believe that may be the solution, but can't grasp the concept or get the syntax correct.
Thanks
Perry

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

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

Mark Cowne One Orange Chip Points: 26954 More actions · Answer 1

SELECT SUM(CASE WHEN tblUseOfForce.Taser=1 THEN 1 ELSE 0 END) AS Taser,

SUM(CASE WHEN tblUseOfForce.PhysicalForce=1 THEN 1 ELSE 0 END) AS PhysicalForce

FROM tblUseOfForce INNER JOIN

tblIncident ON tblUseOfForce.EventNumber = tblIncident.EventNumber

Where (Year(DateReceived) = 2008)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 2

--this one returns a count of all

SELECT count( case when tblUseOfForce.PhysicalForce = 1 then tblUseOfForce.Taser else null end ) AS Taser

, count( case when tblUseOfForce.PhysicalForce = 1 then tblUseOfForce.PhysicalForce else null end ) AS Taser

FROM tblUseOfForce INNER JOIN

tblIncident ON tblUseOfForce.EventNumber = tblIncident.EventNumber

Where (Year(DateReceived) = 2008) AND tblUseOfForce.taser = 1

--this one returns the distinct count of each. no duplicates

SELECT count( distinct case when tblUseOfForce.PhysicalForce = 1 then tblUseOfForce.Taser else null end ) AS Taser

, count( distinct case when tblUseOfForce.PhysicalForce = 1 then tblUseOfForce.PhysicalForce else null end ) AS Taser

FROM tblUseOfForce INNER JOIN

tblIncident

ON tblUseOfForce.EventNumber = tblIncident.EventNumber

Where (Year(DateReceived) = 2008)

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 3

oops...sorry didnt remove constraint

--this one returns a count of all

SELECT count( case when tblUseOfForce.PhysicalForce = 1 then tblUseOfForce.Taser else null end ) AS Taser

, count( case when tblUseOfForce.PhysicalForce = 1 then tblUseOfForce.PhysicalForce else null end ) AS Taser

FROM tblUseOfForce INNER JOIN

tblIncident ON tblUseOfForce.EventNumber = tblIncident.EventNumber

Where (Year(DateReceived) = 2008)

--this one returns the distinct count of each. no duplicates

SELECT count( distinct case when tblUseOfForce.PhysicalForce = 1 then tblUseOfForce.Taser else null end ) AS Taser

, count( distinct case when tblUseOfForce.PhysicalForce = 1 then tblUseOfForce.PhysicalForce else null end ) AS Taser

FROM tblUseOfForce INNER JOIN

tblIncident

ON tblUseOfForce.EventNumber = tblIncident.EventNumber

Where (Year(DateReceived) = 2008)

ppierce10 SSC Enthusiast Points: 107 More actions · Answer 4

Thanks Mr. Or Mrs. 500 that worked great.

Grasshopper your soultion seemed to return the same count for both columns?

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 5

The funny thing is after i submitted it i actually had to do the exact same thing for work and tried the same approach. Once i started testing the results i realized the err in my ways. I'm not 100% today it would seem. Funny. Must have one leg out the door. Perhaps i should lay off the help for the day. Creating more problems than solutions.;)