How to aggregate the data without duplicates in Crosstab query

Question

I have a sample table in MySQL like this:(The actual table has much more races and schools)

**race** |  **school**  | **name**
White    |     MIT      |   Jack 
White    |     MIT      |   Li
White    |     MIT      |   Don
White    |    Fordham   |   Dan
White    |    Fordham   |   Nan
White    |     NYU      |   Kate
Hispanic |     MIT      |   Jacob
Hispanic |    Fordham   |   Joe
Hispanic |    Fordham   |   Connie
Hispanic |     NYU      |   Sarah
Asian    |     MIT      |   Macro
Asian    |     MIT      |   Denise
Asian    |     MIT      |   Dennie
Asian    |     MIT      |   Sasa
Asian    |     Fordham  |   Mei
Asian    |     Fordham  |   Tian
Asian    |     Fordham  |   John
Asian    |     Fordham  |   Lala
Asian    |     NYU      |   Zed
Asian    |     NYU      |   Ann
Asian    |     NYU      |   Nancy
AmeIndian|     MIT      |   David
AmeIndian|     NYU      |   Lonnie

Firstly, I will aggregate the number of each race and order them descending. The query looks like this: (I know what to do for this part)

**race**    |   **Number**
 Asian      |      11
 White      |      6
 Hispanic   |      4
 AmeIndian  |      2

Finally, I want to get a crosstab query like this: (Just extract the top 3 in term of number of people in each race. Besides, the final query is ordered by the total number of people in each race)

**race**  |   **MIT**    |   **Fordham** |   **NYU**   |    **SUM**
Asian     |     4        |        4      |     3       |      11
White     |     3        |        2      |     1       |      6
Hispanic  |     1        |        2      |     1       |      4

I tried the code listed below. I cannot get the "SUM" column. And the number of people in each race under "MIT" and "Fordham" is the same and has duplicates.(refer to need to return two sets of data with two different where clauses) I think it is because I want to order by the total number of people in each race, and order by the total number. However, the order of number of people of each race is different among different schools.

SELECT race,
   COUNT(IF(school="MIT",race,0)) as MIT,
   COUNT(IF(school="Fordham",race,0)) as Fordham
FROM table_name
GROUP by race

Subin Chalil · Accepted Answer · 2015-11-19T16:47:22.367

0

Instead of COUNT() it should be SUM().

SELECT race,
      SUM(IF(school='MIT',1,0)) as MIT,
      SUM(IF(school='Fordham',1,0)) as Fordham,
      SUM(IF(school='NYU',1,0)) AS NYU,
      (SUM(IF(school='MIT',1,0))+SUM(IF(school='Fordham',1,0))+SUM(IF(school='NYU',1,0))) AS Total_Sum
FROM table_name
GROUP by race

Hope this helps.

edited Nov 19 '15 at 16:47

answered Nov 19 '15 at 04:44

Subin Chalil

3,531
2
24
38

Thanks. It is helpful. It is near. But the Count(*) as Total_Sum will sum everything on that race including AmeIndian.However, if I just want to sum the number of people in term of top 3 or the number of school I want to show? – Zed Fung Nov 19 '15 at 16:21

score 0 · Answer 2 · answered Nov 19 '15 at 04:55

You can use a dynamic sql query to achieve this.

QUERY

set @query = null;
select
group_concat(distinct
    concat(
      'count(case when school = ''',
      school, ''' then school end) as ',school
    )
  ) into @query
from your_table_name ;

set @query = concat('select race, ', @query, ',count(*) as `SUM` from your_table_name 
                  group by race order by count(*) desc limit 3
');

prepare stmt from @query;
execute stmt;
deallocate prepare stmt;

OUTPUT

+----------+-----+---------+-----+-----+
|     race | MIT | Fordham | NYU | SUM |
+----------+-----+---------+-----+-----+
|    Asian |   4 |       4 |   3 |  11 |
|    White |   3 |       2 |   1 |   6 |
| Hispanic |   1 |       2 |   1 |   4 |
+----------+-----+---------+-----+-----+

SQL Fiddle

score 0 · Answer 3 · answered Nov 19 '15 at 07:14

firstly:

select * from 
(
    select count(*) as count,race from table1 group by race
) as data
order by data.count desc;

finally

select data.* from (
SELECT race,
      SUM(IF(school='MIT',1,0)) as MIT,
      SUM(IF(school='Fordham',1,0)) as Fordham,
      SUM(IF(school='NYU',1,0)) AS NYU,
      COUNT(*) AS total_Sum
FROM table1
GROUP by race ) as data
order by data.total_Sum desc;

How to aggregate the data without duplicates in Crosstab query

3 Answers3

SQL Fiddle