MySQL - Conditional SELECT to output separate columns

Question

I am trying to get around an apparently simple problem in MySQL: I have a table composed by 3 fields and I would like to form a query that returns a field selected as DISTINCT and two more columns which return a COUNT of the remaining two fields when a specific condition is met.

To illustrate (columns named first, second and third left to right):

| VzokAAD | aaa | 0 |
| VziAAAT | bbb | 0 |
| VziAAAT | ccc | 1 |
| VziAAAT | ddd | 0 |
| W0cZAAT | eee | 1 |
| VziNAAT | fff | 1 |
| VzpqAAD | ggg | 1 |
| VzpqAAD | hhh | 1 |

My current query is structured as follows:

SELECT DISTINCT(first) AS field_one, COUNT(second) 
FROM table WHERE third = 0 GROUP BY field_one;

So the above query will return:

 | VzokAAD | 1 |
 | VziAAAT | 2 |

I can change the query to something like this:

SELECT DISTINCT(first) AS field_one, COUNT(second) 
FROM table WHERE third = 1 GROUP BY field_one;

And it will return:

 | VzpqAAD | 2 |
 | VziNAAT | 1 |
 | W0cZAAT | 1 |
 | VziAAAT | 1 |

How do I get both queries combined together so that, I get the first column GROUPED and DISTINCT and two additional columns with the COUNT of third =1 and third =0 respectively.

Basically something like this (wrong of course):

SELECT DISTINCT(first) AS field_one, COUNT(second WHERE third = 1) 
AS alpha, COUNT(second WHERE third = 0) AS beta FROM table GROUP BY field_one;

I tried to use CASE and IF control flow functions however I only managed to get the results defined in a single column.

Thanks to anyone willing to lend a hand!

Sagar Joon · Accepted Answer · 2015-03-17 07:55:38Z

1

You can simply use this query :

 select first , SUM(IF(third=0,1,0)) as Col_zero, SUM(IF(third=1,1,0)) as Col_one
 from table
 group by first;

answered Mar 17, 2015 at 7:55

Sagar Joon

1,41715 silver badges23 bronze badges

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

1 Comment

bashintosh Over a year ago

Absolutely fantastic - worked right away just as I wanted it! Thanks Sugar Joon!

Stanislovas Kalašnikovas · Accepted Answer · 2015-03-17 09:45:17Z

1

You can use UNION in following:

SELECT first AS field_one, 
       COUNT(second) 
FROM table 
WHERE third = 0 
GROUP BY field_one;
UNION
SELECT first AS field_one, 
       COUNT(second) 
FROM table 
WHERE third = 1 
GROUP BY field_one;

Also how about IN?

SELECT DISTINCT(first) AS field_one, 
       COUNT(second) 
FROM table 
WHERE third IN(0, 1) 
GROUP BY field_one;

edited Mar 17, 2015 at 9:45

answered Mar 17, 2015 at 6:50

Stanislovas Kalašnikovas

9,0735 gold badges36 silver badges60 bronze badges

2 Comments

Strawberry Over a year ago

DISTINCT is not a function

bashintosh Over a year ago

The problem with UNION is that it merged all the output into one column :(

Mobasher Fasihy · Accepted Answer · 2015-03-17 09:27:15Z

0

You can do like this:

SELECT 
      first AS field_one, 
      (CASE WHEN third =0 THEN COUNT(second) END)AS count1, 
      (CASE WHEN third=1 THEN COUNT(second) END)AS count2
FROM my_table
WHERE third IN('0','1')
GROUP BY field_one

edited Mar 17, 2015 at 9:27

answered Mar 17, 2015 at 6:57

Mobasher Fasihy

1,0612 gold badges10 silver badges19 bronze badges

2 Comments

Sagar Joon Over a year ago

why Distinct is required here?

bashintosh Over a year ago

This was an interesting alternative however I found that it would only count one of the two output columns (count1 or count2) leaving the other column as NULL. I am not completely sure of the reason but if anybody knows it would be a good tip to learn.

Collectives™ on Stack Overflow

MySQL - Conditional SELECT to output separate columns

3 Answers 3

1 Comment

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related