MySQL IF statement then COUNT value in column

Question

I'm new to MySQL (using SQLite) and working on a more complex query

Assume my database is something like

tablename = "stages"

id | results | stage    | parent_id
1  | no      | 'stage1' | 1
2  | no      | 'stage1' | 1
3  | yes     | 'stage1' | 2
4  | no      | 'stage2' | 2

The query I'm trying to do is add a COUNT column, counting the number of results where stage = stage1

The desired results would be:

parent_id | stage | stage_no_count | stage_yes_count

How would I go about doing this?

EDIT: For all of the answers, THANK YOU! I was just trying to understand the logic that goes into a simple query like this. I can re-apply it to a more complex query, but was curious how I'd do it for a simple example.

Which MySQL version ? `SELECT VERSION()` Also see [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) for providing example data and expected results. — Raymond Nijland, Mar 05 '19 at 15:20

score 2 · Answer 1 · answered Mar 05 '19 at 15:26

With MySQL, we can use conditional aggregation. Shorthand version something like this:

SELECT t.parent_id
     , t.stage
     , SUM(t.results = 'no')    AS stage_no_count
     , SUM(t.results = 'yes')   AS stage_yes_count
  FROM stages t
 GROUP
    BY t.parent_id
     , t.stage

More portable and compliant with ANSI SQL standard

SELECT t.parent_id
     , t.stage
     , SUM(CASE t.results WHEN 'no'  THEN 1 ELSE 0 END)  AS stage_no_count
     , SUM(CASE t.results WHEN 'yes' THEN 1 ELSE 0 END)  AS stage_yes_count
  FROM stages t
 GROUP
    BY t.parent_id
     , t.stage

(There is a slight difference with handling of NULL values for results, whether we return a 0 or a NULL)

won't this count ALL of the stage yes or no count? not by stage1 / stage 2 — Morgan Allen, Mar 05 '19 at 15:29

Raymond Nijland · Accepted Answer · 2019-03-05T15:32:51.347

1

Not knowing the example data and or the expected result.
I assuming you want to PIVOT on parent_id and stage

With COUNT

SELECT
   parent_id
 , stage
 , COUNT(CASE WHEN results = 'no' THEN 1 ELSE NULL END) AS stage_no_count
 , COUNT(CASE WHEN results = 'yes' THEN 1 ELSE NULL END) AS stage_yes_count
FROM 
 stages
GROUP BY
  parent_id
, stage
# if the order is important in the results
ORDER BY 
 parent_id ASC

With SUM

SELECT
   parent_id
 , stage
 , SUM(CASE WHEN results = 'no' THEN 1 ELSE 0 END) AS stage_no_count
 , SUM(CASE WHEN results = 'yes' THEN 1 ELSE 0 END) AS stage_yes_count
FROM 
 stages
GROUP BY
  parent_id
, stage
# if the order is important in the results
ORDER BY 
 parent_id ASC

edited Mar 05 '19 at 15:32

answered Mar 05 '19 at 15:27

Raymond Nijland

11,488
2
22
34

1

What? `COUNT(0)`? Nevermind.. seems to work for OP. Who needs ecact numbers? ;-) – Paul Spiegel Mar 05 '19 at 15:29
indeed `COUNT(0)` converts to `1` what was i thinking? thanks @PaulSpiegel i need coffee.. – Raymond Nijland Mar 05 '19 at 15:33
wait, won't this count ALL of the stage yes / no? ie - if stage 1 is no and stage 2 is no, I'd want to coun't just the "no" for stage 1 – Morgan Allen Mar 05 '19 at 15:34
see mine comment under your question if mine answer does not help. @MorganAllen – Raymond Nijland Mar 05 '19 at 15:37
I see, the group BY stage solves that problem – Morgan Allen Mar 05 '19 at 15:37
@MorganAllen if your SQL is now `SELECT parent_id , stage , COUNT(CASE WHEN results = 'no' THEN 1 ELSE NULL END) AS stage_no_count , COUNT(CASE WHEN results = 'yes' THEN 1 ELSE NULL END) AS stage_yes_count FROM stages GROUP BY stage` then your query is not valid and the data within the parent_id column can't be trusted to be correct. – Raymond Nijland Mar 05 '19 at 15:38
1

sorry for the confusion, your answer worked! I didn't understand how it worked until I played with it. it makes sense now and works as expected – Morgan Allen Mar 05 '19 at 15:40

MySQL IF statement then COUNT value in column

2 Answers2