Returning a recordcount from a subquery in a result set.

Question

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.

Answer 1

How about this hack to do it without a subquery:

SELECT
    recordID,
    GroupIdentifier,
    COUNT() AS total,
    SUM(intActingAsBoolean = 1) AS Approved 
FROM table
WHERE date_format(Datevalue, '%Y%m%d') BETWEEN 'startDate' AND 'endDate'
GROUP BY groupIdentifier

Answer 2

It probably is not the best approach, but you could write a function that will return the value you are looking for.