• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 552
  • Last Modified:

count WHERE in sql query

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!
0
RJT_VT
Asked:
RJT_VT
  • 4
  • 2
2 Solutions
 
CluskittCommented:
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
0
 
RJT_VTAuthor 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?
0
 
IrogSintaCommented:
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

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
CluskittCommented:
My query was for SQL. I hadn't noticed it was Access. IrogSinta's code should work.
0
 
RJT_VTAuthor Commented:
The Iif statement works perfectly.

Thank you both! AndCluskitt, I apologize that I wasn't explicit about using Access.
0
 
CluskittCommented:
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 ;)
0
 
CluskittCommented:
I shouldn't get points for this question. My answer wasn't either correct or helpful. You should award full points to IrogSinta.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now