Mysql count of values on pivoting or transposing

Question

I have a table in my database

+------+------+----------+
| id   | dept | location |
+------+------+----------+
|    1 | eee  | chennai  |
|    2 | ece  | chennai  |
|    3 | eee  | chennai  |
|    4 | ece  | chennai  |
|    5 | eee  | cbe      |
|    6 | ece  | trichy   |
|    7 | mech | madurai  |
+------+------+----------+

I need the count of id on transposing one of the column. Can anyone suggest me the query to get the result below

+---------+------+-----+------+
| location| eee  | ece | mech |
+---------+------+-----+------+
| chennai | 2    | 2   |  0   |
| cbe     | 1    | 0   |  0   |
| trichy  | 0    | 1   |  0   |
| madurai | 0    | 0   |  1   |
+---------+------+-----+------+

Thank you for any help

score 3 · Accepted Answer · answered Jul 19 '18 at 05:22

3

select location as city,
       sum(dept = 'eee') as eee,
       sum(dept = 'ece') as ece,
       sum(dept = 'mech') as mech
from your_table
group by location

answered Jul 19 '18 at 05:22

juergen d

201,996
37
293
362

Thank you juergen d. It worked well. – Jagadesh jakes Jul 19 '18 at 05:33
How many times are you going to answer the exactly the same question without marking it a duplicate? For example https://stackoverflow.com/questions/36515502/group-rows-into-columns-with-sum or https://stackoverflow.com/questions/39767137/mysql-group-by-a-column-and-show-values-in-different-columns – Shadow Jul 19 '18 at 05:40

score 2 · Answer 2 · answered Jul 19 '18 at 05:28

2

TRY SUM with CASE

create table test(id INT, dept VARCHAR(20), location VARCHAR(100))
insert into test values
(1,'eee','chennai'),
(2,'ece','chennai'),
(3,'eee','chennai'),
(4,'ece','chennai'),
(5,'eee','cbe'),
(6,'ece','trichy'),
(7,'mech','madurai')

SELECT 
    location AS city,
    SUM(CASE WHEN dept = 'eee' THEN 1 ELSE 0 END) eee,
    SUM(CASE WHEN dept = 'ece' THEN 1 ELSE 0 END) ece,
    SUM(CASE WHEN dept = 'mech' THEN 1 ELSE 0 END) mech
FROM test
GROUP BY location

OUTPUT

city    eee ece mech
cbe     1   0   0
chennai 2   2   0
madurai 0   0   1
trichy  0   1   0

answered Jul 19 '18 at 05:28

Shushil Bohara

5,556
2
15
32

Thank you Susang. – Jagadesh jakes Jul 19 '18 at 05:35
You are welcome :) – Shushil Bohara Jul 19 '18 at 09:02

score 1 · Answer 3 · answered Jul 19 '18 at 05:25

CREATE TABLE #TABLE1
    ([ID] INT, [DEPT] VARCHAR(4), [LOCATION] VARCHAR(7))
;

INSERT INTO #TABLE1
    ([ID], [DEPT], [LOCATION])
VALUES
    (1, 'EEE', 'CHENNAI'),
    (2, 'ECE', 'CHENNAI'),
    (3, 'EEE', 'CHENNAI'),
    (4, 'ECE', 'CHENNAI'),
    (5, 'EEE', 'CBE'),
    (6, 'ECE', 'TRICHY'),
    (7, 'MECH', 'MADURAI')
;


SELECT [LOCATION], COUNT(CASE WHEN DEPT = 'EEE' THEN [DEPT] END ) EEE ,
COUNT(CASE WHEN DEPT = 'ECE' THEN DEPT END )  ECE
,COUNT(CASE WHEN DEPT = 'MECH' THEN DEPT END ) MECH FROM #TABLE1
GROUP BY [LOCATION]

output

LOCATION    EEE ECE MECH
CBE          1   0   0
CHENNAI      2   2   0
MADURAI      0   0   1
TRICHY       0   1   0

It is MySQL, not SQL-Server – juergen d Jul 19 '18 at 05:29 — juergen d, Jul 19 '18 at 05:29
Thanks for trying this Chanukya. It worked. – Jagadesh jakes Jul 19 '18 at 05:34 — Jagadesh jakes, Jul 19 '18 at 05:34

Mysql count of values on pivoting or transposing

3 Answers3