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!

Bill Karwin · Accepted Answer · 2011-12-09 00:34:15Z

3

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`

answered Dec 9, 2011 at 0:34

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Complex MySQL COUNT query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related