Help With getting a count query to work

Question

Post reply

Help With getting a count query to work

ppierce10

SSC Journeyman

Points: 97
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: 26865 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 Journeyman Points: 97 More actions · Answer 4

ppierce10

SSC Journeyman

Points: 97

November 25, 2009 at 8:44 am

#1084105

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.;)