I am new Access and I framing a query to group a table based on a field and get a count of records based on a condition.
Table:
Category Status
Pen Open
Pencil Open
Pen Closed
Pencil Closed
I am looking for a query which gives me an output as shown below:
Category Open Closed
Pen 1 1
Pencil 1 1
So far I tried, SELECT Category, COUNT(Status='Open'), Count(Status='Closed') FROM table GROUP BY Category; This query does not solve the issue.
TIA
One way to do this is to do conditional aggregation using the sum and iif functions:
select
category,
sum(iif(status='Open',1,0)) as Open,
sum(iif(status='Closed',1,0)) as Closed,
from table
group by category
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments