Mysql Question posted in
The official MySQL documentation can be found here.

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;
Tags:

3

Answers


  1. 0

    Your attempt is good, you just need to rewrite COALESCE(NULLIF(team,''), name) to COALESCE(team, name) because COALESCE will only move on to the next parameter if the current parameter is NULL. Since'' != NULL the name column will never be selected.

    Something like:

    SELECT COALESCE(team, name) as name, 
   CASE when team IS NULL THEN 0 ELSE 1 END as group 
FROM tournament 
ORDER BY COALESCE(team, name);
    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
  3. 0

    It works also without union like this

    SELECT 
    MAX(name) AS name,
    IF(team = '',0,1) as team
FROM tournament 
GROUP BY team,name
ORDER BY IF(team = '',1,0),name ASC;

    result:

    name    team
Andrew  1
Carol   1
Max     1
Neil    1
Warren  1
William 1
Donna   0
Keith   0
Phil    0
William 0

    click here for a sample

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search