Mysql split row sum by count

Question

I have the following tables http://sqlfiddle.com/#!2/d0a3d

sp | product | exp
1    A       | 50
1    B         50
1    A         100
1    B         100
2    B         200
2    C         200
3    A         50
3    B         50

Technical I want to divide exp to total number of products associated with t_id

The final result should be
 sp |  A   | B   |  C
 1  | 150  | 150 |  0
 2  |  0   | 200 | 200
 3  |  50  | 50  |  0

They will be associated with other t_ids. This is just snapshot of table data — kayra, May 27 '14 at 13:15
possible duplicate of [MySQL pivot table query with dynamic columns](http://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns) — OGHaza, May 27 '14 at 13:43

Kickstart · Answer 1 · 2014-05-27T13:43:35.380

1

Can be done like this (done to match your sql fiddle columns):-

SELECT b.sp_id, 
    SUM(IF(a.product = 'A', b.exp, 0)) AS A,
    SUM(IF(a.product = 'B', b.exp, 0)) AS B,
    SUM(IF(a.product = 'C', b.exp, 0)) AS C
FROM topic_product a
INNER JOIN exp_speaker_topic b
ON a.t_id = b.t_id
GROUP BY b.sp_id

But a mess waiting to happen when extra values get added.

EDIT - amended to give what I think you are saying you want.

SELECT sp_id, 
    SUM(IF(product = 'A', avg_exp, 0)) AS A,
    SUM(IF(product = 'B', avg_exp, 0)) AS B,
    SUM(IF(product = 'C', avg_exp, 0)) AS C
FROM 
(
    SELECT sp_id, a.product, exp / Sub1.product_count AS avg_exp
    FROM topic_product a
    INNER JOIN exp_speaker_topic b
    ON a.t_id = b.t_id
    INNER JOIN
    (
        SELECT t_id, COUNT(*) AS product_count
        FROM topic_product 
        GROUP BY t_id
    ) Sub1
    ON a.t_id = Sub1.t_id
) Sub2
GROUP BY sp_id

SQL fiddle:-

http://sqlfiddle.com/#!2/d0a3d/33

edited May 27 '14 at 13:43

answered May 27 '14 at 13:26

Kickstart

21,403
2
21
33

I know this query, however the problem is with record when exp_speaker_topic record has t_id whose products are A, B. so I want to divide expense by number of products associated with that t_id. As you can see in question, I want to derive the first table and query is not doing it. – kayra May 27 '14 at 13:30
The query does derive the data, but this is the first you have mentioned about dividing an expense by different products. Can you modify your question to have sample input data and examples of what you want the results to be when shared over multiple products – Kickstart May 27 '14 at 13:32
Amended for what I think you want based on your comment. – Kickstart May 27 '14 at 13:44

Linger · Answer 2 · 2014-05-27T13:58:35.027

Here is the SQL Fiddle demonstrating the below queries.

You could also use CASE statements like the following:

SELECT e.sp_id, 
  SUM(CASE WHEN t.product = 'A' THEN e.exp ELSE 0 END) AS A,
  SUM(CASE WHEN t.product = 'B' THEN e.exp ELSE 0 END) AS B,
  SUM(CASE WHEN t.product = 'C' THEN e.exp ELSE 0 END) AS C
FROM topic_product t INNER JOIN exp_speaker_topic e ON t.t_id = e.t_id
GROUP BY e.sp_id;

If you want to divide by the number of record use the following:

SELECT e.sp_id, 
  SUM(CASE WHEN t.product = 'A' THEN e.exp ELSE 0 END) / 
  SUM(CASE WHEN t.product = 'A' THEN 1 ELSE 0 END) AS A,
  SUM(CASE WHEN t.product = 'B' THEN e.exp ELSE 0 END) / 
  SUM(CASE WHEN t.product = 'B' THEN 1 ELSE 0 END) AS B,
  SUM(CASE WHEN t.product = 'C' THEN e.exp ELSE 0 END) / 
  SUM(CASE WHEN t.product = 'C' THEN 1 ELSE 0 END) AS C
FROM topic_product t INNER JOIN exp_speaker_topic e ON t.t_id = e.t_id
GROUP BY e.sp_id;

If you want to get rid of the Nulls you could use the following:

SELECT m.sp_id,
CASE WHEN ISNULL(m.A) = 0 THEN m.A ELSE 0 END AS A,
CASE WHEN ISNULL(m.B) = 0 THEN m.B ELSE 0 END AS B,
CASE WHEN ISNULL(m.C) = 0 THEN m.C ELSE 0 END AS C
FROM 
(
  SELECT e.sp_id, 
    SUM(CASE WHEN t.product = 'A' THEN e.exp ELSE 0 END) / SUM(CASE WHEN t.product = 'A' THEN 1 ELSE 0 END) AS A,
    SUM(CASE WHEN t.product = 'B' THEN e.exp ELSE 0 END) / SUM(CASE WHEN t.product = 'B' THEN 1 ELSE 0 END) AS B,
    SUM(CASE WHEN t.product = 'C' THEN e.exp ELSE 0 END) / SUM(CASE WHEN t.product = 'C' THEN 1 ELSE 0 END) AS C
  FROM topic_product t INNER JOIN exp_speaker_topic e ON t.t_id = e.t_id
  GROUP BY e.sp_id
) AS m;

score 0 · Answer 3 · answered May 27 '14 at 13:43

0

OK, slow day. Just do the following, and handle missing results and display logic in the presentation layer/application-level code (e.g. a simple php loop acting upon an ordered array)...

SELECT p.product
     , s.sp_id
     , SUM(s.exp/x.cnt) total
  FROM topic_product p
  JOIN exp_speaker_topic s
    ON s.t_id = p.t_id
  JOIN
     (  SELECT t_id
             , COUNT(0) cnt
          FROM topic_product 
         GROUP 
            BY t_id
     ) x
    ON x.t_id = p.t_id
 GROUP  
    BY sp_id,product;

answered May 27 '14 at 13:43

Strawberry

33,750
13
40
57

I am having limitation to use php code, I need to handle everything in query. – kayra May 27 '14 at 13:49
Well @kickstart's on it - ;-) – Strawberry May 27 '14 at 13:57

Mysql split row sum by count

3 Answers3