Complex MySQL COUNT query

Question

Evening folks,

I have a complex MySQL COUNT query I am trying to perform and am looking for the best way to do it.

• In our system, we have References. Each Reference can have many (or no) Income Sources, each of which can be validated or not (status). We have a Reference table and an Income table - each row in the Income table points back to Reference with reference_id
• On our 'Awaiting' page (the screen that shows each Income that is yet to be validated), we show it grouped by Reference. So you may, for example, see Mr John Smith has 3 Income Sources.
• We want it to show something like "2 of 3 Validated" beside each row
• My problem is writing the query that figures this out!

What I have been trying to do is this, using a combination of PHP and MySQL to bridge the gap where SQL (or my knowledge) falls short:

First, select a COUNT of the number of incomes associated with each reference:

SELECT `reference_id`, COUNT(status) AS status_count
FROM (`income`)
WHERE `income`.`status` =  0
GROUP BY `reference_id` 

Next, having used PHP to generate a WHERE IN clause, proceed to COUNT the number of confirmed references from these:

SELECT `reference_id`, COUNT(status) AS status_count
FROM (`income`)
WHERE `reference_id` IN ('8469', '78969', '126613', ..... etc 
AND status = 1
GROUP BY `reference_id` 

However this doesn't work. It returns 0 rows.

Any way to achieve what I'm after?

Thanks!

Answer 1

In MySQL, you can SUM() on a boolean expression to get a count of the rows where that expression is true. You can do this because MySQL treats true as the integer 1 and false as the integer 0.

SELECT `reference_id`, 
  SUM(`status` = 1) AS `validated_count`, 
  COUNT(*) AS `total_count` 
FROM `income`
GROUP BY `reference_id`