Returning a recordcount from a subquery in a result set.
- by KeRiCr
I am attempting to return a rowcount from a subquery as part of a result set. Here is a sample that I've tried that didn't work:
SELECT recordID
, GroupIdentifier
, count() AS total
, (SELECT COUNT() FROM table WHERE intActingAsBoolean = 1) AS Approved
FROM table
WHERE date_format(Datevalue, '%Y%m%d') BETWEEN 'startDate' AND 'endDate'
GROUP BY groupIdentifier
What I'm attempting to return for 'Approved' is the number of records for the grouped value where intActingAsBoolean = 1. I have also tried modifying the where clause by giving the main query a table alias and applying an AND clause to match the groupidentifier in the subquery to the main query. None of these are returning the correct results. The query as written returns all records in the table where intActingAsBoolean = 1.
This query is being run against a MySQL database.
