CODEWITHSUNDEEP
mysqlsqlselecthaving
pillarOfLight
pillarOfLight

Reputation: 8982

count items that satisfy having query

suppose I have

SELECT * FROM table t
GROUP BY j
HAVING condition_one OR condition_two OR condition_three

how can I modify the query such that it returns the COUNT of how many rows satisfied the three different conditions in having clause

so ideally the output would have something like:

condition_one: 100
condition_two: 200
condition_three: 300

whereby there are 100 items satisfying condition_one, 200 satisfying condition_two etc

Upvotes: 0

Views: 829

Answers (2)

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

If you use a having clause you will actually loose those records. So you can't use a having clause. Apart from that, if you use ORs, then you won't have which condition resulted in true.

So, what you should use is a derived table that holds all grouped values. Once you have that, query that table to get the counts of each of them. However, that solution will give you three columns instead of three rows. Let's go for that one that is the easier one:

SELECT
    SUM(condition_one) condition_one,
    SUM(condition_two) condition_two,
    SUM(condition_three) condition_three
FROM (
    SELECT * FROM t
    GROUP BY j
) final

Note that condition_one is really a condition, such as age = 23, but conditions in MySQL return 0 for false and 1 for true so you can actually SUM conditions.

Now, if you want to have them in rows, that's a little bit more complicated because you'll have to UNION each value independently:

SELECT 'condition_one: ' Condition, SUM(condition_one) ConditionCount FROM (
    SELECT * FROM t GROUP BY j
) s1
UNION ALL
SELECT 'condition_two: ', SUM(condition_two) FROM (
    SELECT * FROM t GROUP BY j
) s2
UNION ALL
SELECT 'condition_three: ', SUM(condition_three) FROM (
    SELECT * FROM t GROUP BY j
) s3

Or at least, that's the best way I can think of right now. Hope this helps!

Upvotes: 0

Eugen Rieck
Eugen Rieck

Reputation: 65284

SELECT
  SUM(IF(condition_one,1,0)) AS condition_one,
  SUM(IF(condition_two,1,0)) AS condition_two,
  SUM(IF(condition_three,1,0)) AS condition_three
FROM (
  SELECT * FROM t
  GROUP BY j
) AS baseview

Upvotes: 2

Related Questions