MySQL Pivot Query with Sum of column value

Question

Table 1(reports) - Structure with Data

id  distributorId clientId amount
1       1            162    2500
2       2            163    3500
3       3            203    4500
4       6            157    5500
5       1            162    1500
6       3            163    2000
7       3            162    1000

Table 2(distributor) - Structure with Data

id    distributor
1        Dis A
2        Dis B
3        Dis C
6        Dis D

Table 3(clients) - Structure with Data

id    client_name
162     Client A
163     Client B
203     Client C
157     Client D

Desired Output Using the above defined 3 tables:

client_name    Dis A    Dis B    Dis C    Dis D
 Client A      4000      0       1000      0
 Client B       0        3500    2000      0
 Client C       0        0       4500      0
 Client D       0        0        0       5500

What is your question? Have you tried any query? What is the error ? — Hamza Zafeer
– Hamza Zafeer, Commented Jun 14, 2016 at 0:32
@TimBiegeleisen Yes. Only 4 distributors but would prefer a dynamic query as in future they might change. — Saurabh
– Saurabh, Commented Jun 14, 2016 at 0:40
You should take up Gordon's advice. A dynamic MySQL pivot would be quite a bit of code. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jun 14, 2016 at 0:42

Blank · Accepted Answer · 2016-06-14 01:37:29Z

For the fixed pivot sql, try this:

select
    t.client_name,
    sum(if(t.distributor = 'Dis A', t.amount, 0)) as `Dis A`,
    sum(if(t.distributor = 'Dis B', t.amount, 0)) as `Dis B`,
    sum(if(t.distributor = 'Dis C', t.amount, 0)) as `Dis C`,
    sum(if(t.distributor = 'Dis D', t.amount, 0)) as `Dis D`
from (
    select c.id, c.client_name, d.distributor, r.amount
    from clients c
    left join reports r on c.id = r.clientId
    left join distributor d on d.id = r.distributorId
)t
group by t.id
order by t.client_name

SQLFiddle DEMO HERE

For the dynamic pivot sql, try this:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(if(t.distributor = ''',
      distributor,
      ''', t.amount, 0)) AS `',
      distributor ,'`'
    )
  ) INTO @sql
FROM distributor;
SET @sql = CONCAT('SELECT t.client_name, ', @sql, ' FROM (
    select c.id, c.client_name, d.distributor, r.amount
    from clients c
    left join reports r on c.id = r.clientId
    left join distributor d on d.id = r.distributorId
)t
group by t.id
order by t.client_name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SQLFiddle DEMO HERE

Cesar Williams · Accepted Answer · 2022-05-05 02:57:59Z

0

Try this one.

with new_table as
(
select 
    T3.client_name as client,
    sum(case when T1.distributorId = '1 ' then amount end) as  'Dis A',
    sum(case when T1.distributorId = '2 ' then amount end) as  'Dis B',
    sum(case when T1.distributorId = '3 ' then amount end) as  'Dis C',
    sum(case when T1.distributorId = '6 ' then amount end) as  'Dis D'
FROM reports T1
JOIN distributor T2 on T2.id = T1.distributorId
JOIN clients T3 on T3.id = T1.clientId 
)

select 
    client,
    Dis A ,
    Dis B,
    Dis C,
    Dis D
FROM New_Table
group by 1

answered May 5, 2022 at 2:57

Cesar Williams

1

1 Comment

Community Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

MySQL Pivot Query with Sum of column value

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related