CODEWITHSUNDEEP
sqlms-access
pizookie
pizookie

Reputation: 47

How to "count" two different criteria from the same column in a table?

I'm trying to write a simple query that groups by day, populates one column with the count of "valid" AND "running" points in that day, and another column with the count of all "valid" points. So the result columns will be: "Day", "RunNum", "TotalNum". It reads from a table with the columns "TheDate", "Running" and "ValidPoint".

I can create the "RunNum" and "TotalNum" columns individually by changing the "FROM" statement to suit the specific criteria of the column. However, I am a complete beginner with SQL and I am in need of some guidance on how to read two different criteria from one table.

This is what I have written to create RunNum:

SELECT TheDate AS Day, count(Running) AS RunNum
FROM Source_Table
WHERE Running = "Runtime" AND ValidPoint = "yes"
GROUP BY TheDate;

And this to create TotalNum:

SELECT TheDate AS Day, count(Running) AS TotalNum
FROM Source_Table
WHERE ValidPoint = "yes"
GROUP BY TheDate;

I have tried using the SUM(CASE WHEN...) method outlined in this post, but it kept throwing a syntax error message when I tried to run it:

SELECT TheDate As Day, SUM(CASE WHEN Running = "Runtime" THEN 1 ELSE 0 END) AS RunNum, COUNT(Running) AS TotalNum
FROM Source_Table
WHERE ValidPoint = "yes"
GROUP BY TheDate;

I suspect I will need to use table "aliases" in the from statement, but everything I have read about them so far has been confusing. Any help or guidance in the right direction for further research would be much appreciated. Thank you for taking the time to read this!

Upvotes: 2

Views: 273

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271211

In MS Access, conditional aggregation uses IIF() instead of CASE:

SELECT TheDate As Day,
       SUM(IIF(Running = "Runtime", 1, 0)) AS RunNum,
       COUNT(*) AS TotalNum
FROM Source_Table
WHERE ValidPoint = "yes"
GROUP BY TheDate;

Upvotes: 2

Related Questions