CODEWITHSUNDEEP
mysqlsql
John No
John No

Reputation: 35

MYSQL splitting two different values in a single column into two columns

Database Table

I am trying to make a single query where I split the two values of 'Y' and 'N' into two different columns based on the id and count the total number of times they appear for each id.

SELECT exerciseId, count(frustrated) Frustrated from selfreportfrustration where frustrated = 'Y' group by exerciseId;

SELECT exerciseId, count(frustrated) NotFrustrated from selfreportfrustration where frustrated = 'N' group by exerciseId;

So far I have only managed to make one query for each value, but I was hoping to be able to shorten it into one query for my program to work.

Upvotes: 0

Views: 62

Answers (3)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28864

You can use Count() aggregation with If() conditional function.

Try:

SELECT exerciseId, 
       COUNT( IF(frustrated = 'Y', exerciseId, NULL) ) AS frustrated, 
       COUNT( IF(frustrated = 'N', exerciseId, NULL) ) AS not_frustrated 
FROM selfreportfrustration 
GROUP BY exerciseId

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You can use conditional aggregation:

select exerciseId, sum(frustrated = 'Y') as Frustrated,
       sum(frustrated = 'N') as NotFrustrated
from selfreportfrustration
group by exerciseId;

Upvotes: 2

Junjie
Junjie

Reputation: 521

We'll put the 'Y' or 'N' in another column:

SELECT frustrated, exerciseId,
  count(frustrated) Frustrated
from selfreportfrustration
group by frustrated, exerciseId;

Upvotes: 0

Related Questions