• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 557
  • 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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

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