Is my query out of scope for query builder?
I think the query builder is a really useful tool, however I may have a requirement which I don't seem to be able to resolve in QB.
Simplified; I have a table;
Make---------------STP
A. ---------------1
A ---------------1
A ---------------0
B ---------------0
B ---------------0
B ---------------1
C ---------------1
C ---------------1
C ---------------1
C ---------------0
I need a query which outputs a count of distinct values for A and against each, count (or sum) only where STP = '1'
The resultant table would look thus;
Make STP
A. (3) (2)
B. (2). (1)
C. (4). (3)
I've compiled the following in QB however the output is duplicating the same totals for each column; Make & STP.
SELECT Sum(DEVICE_STP) AS SumDEVICE_STP, Count(DEVICE_Make) AS CountDEVICE_Make
FROM TACdb2
WHERE DEVICE_STP = 1
GROUP BY DEVICE_Make
ORDER BY DEVICE_STP DESC
Grateful for a nudge in a direction that corrects the above.
Resolved:
For my solution, I researched and hand wrote the sql.
On this occasion QB didn't have the scope to assist unfortunately.