Search code examples
sqlmysql

MYSQL query to pull data by groups

I have following data in tourament MySQL table.

id name team
1 Warren A
2 Carol B
3 Donna
4 William A
5 Andrew C
6 Neil A
7 Max A
8 Phil
9 William
10 Keith
11 Phil

I am trying to create a query to output the data in the following form to process it further.

name team
A 1
B 1
C 1
Donna 0
Keith 0
Phil 0
Phil 0
William 0

Please have a look here for data.

Current attempt:

select COALESCE(NULLIF(`team`,''), `name`) as name, 
case
    when `team` = '' THEN false
    when `team` != '' THEN true
end as `group`
from `tournament` order by COALESCE(NULLIF(`team`, ''), `name`) ASC;
Solution

  • Your approach is good! Try the below code to achieve the expected output:

    SELECT 
    team AS name, 
    case
        when `team` = '' THEN false
        when `team` != '' THEN true
    end as `group` 
FROM tournament 
WHERE team IS NOT NULL AND team != '' 
GROUP BY team 
UNION ALL 
SELECT name, 0 AS team 
FROM tournament 
WHERE team IS NULL OR team = ''
ORDER BY name;