We help IT Professionals succeed at work.

count WHERE in sql query

RJT_VT
RJT_VT asked
on
Hi experts,

Despite extensive searching here on EE, I have yet to figure this one out (I'm also sort of a newbie):

I'm trying to write a query that's grouped by several fields, but I also want to generate a few summary statistics of other fields along the way.  In particular, I want records where UVA = 1 to be counted and I want records where UVA = 2 to be counted -- both separately. But I don't know where to put the "where"!  Here's what I have so far:

SELECT PubPri([ownership]) AS PrimOwn, tblGenData.Ownership, Count(tblIntData.UVA) AS YesUVA WHERE tblIntData.UVA=1, Count(tblIntData.UVA) AS NoUVA WHERE tblIntData.UVA=2, Avg(tblIntData.THOArea) AS AvgArea, Sum(tblIntData.THOArea) AS TtlArea
FROM tblGenData INNER JOIN tblIntData ON tblGenData.pkOpNum = tblIntData.pkOpNum
GROUP BY PubPri([ownership]), tblGenData.Ownership;

Open in new window

And if I build the query in design view and then toggle to SQL, when I indicate criteria of 1 and 2 for the fields YesUVA and NoUVA, Access sticks in "HAVING (((Count(tblIntData.UVA))=1) AND ((Count(tblIntData.UVA))=2));" at the end of the SQL statement, but I want those criteria to occur as part of the count.

Thanks for your help!
Comment
Watch Question

View Solutions Only

Cluskitt
CERTIFIED EXPERT
Commented:
Don't put a where... just add this to the select:
SUM(CASE WHEN UVA=1 THEN 1 ELSE 0 END) SumUva1, SUM(CASE WHEN UVA=2 THEN 1 ELSE 0 END) SumUva2
RJT_VT

Author

Commented:
Thank  you for your speedy reply, and I've introduced the CASE statement, but I'm trying to generate counts, and Access still doesn't like this:

Count(CASE WHEN UVA=1 THEN 1 ELSE 0 END) AS YesUVA, Count(CASE WHEN UVA=2 THEN 1 ELSE 0 END) AS NoUVA

Do I need to be more explicit about the table I'm referencing, too?
CERTIFIED EXPERT
Commented:
If you're doing this in Access, try this:
SELECT PubPri([ownership]) AS PrimOwn, tblGenData.Ownership, Sum(IIF(tblIntData.UVA=1,1,0)) AS YesUVA, Sum(IIF(tblIntData.UVA=2,1,0)) AS NoUVA, Avg(tblIntData.THOArea) AS AvgArea, Sum(tblIntData.THOArea) AS TtlArea
FROM tblGenData INNER JOIN tblIntData ON tblGenData.pkOpNum = tblIntData.pkOpNum
GROUP BY PubPri([ownership]), tblGenData.Ownership;

Open in new window

Cluskitt
CERTIFIED EXPERT

Commented:
My query was for SQL. I hadn't noticed it was Access. IrogSinta's code should work.
RJT_VT

Author

Commented:
The Iif statement works perfectly.

Thank you both! AndCluskitt, I apologize that I wasn't explicit about using Access.
Cluskitt
CERTIFIED EXPERT

Commented:
Actually, you were. It was part of the tags, the topics and even the title. It was my fault for not noticing. Anyway, as long as it works, that's all that matters ;)
Cluskitt
CERTIFIED EXPERT

Commented:
I shouldn't get points for this question. My answer wasn't either correct or helpful. You should award full points to IrogSinta.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.