MySQL GROUP BY multiple columns from different tables

Question

I've got the following table layouts:

Table Data
+----------+-------------------------+
| Field    | Type                    |
+----------+-------------------------+
| type     | enum('type_b','type_a') |
| type_id  | int(11) unsigned        |
| data     | bigint(20) unsigned     |
+----------+-------------------------+

Table A and B:

+--------------+------------------+
| Field        | Type             |
+--------------+------------------+
| id           | int(11) unsigned |
| customer_id  | int(11) unsigned |
| ...                             |
+--------------+------------------+

In table Data there is some messurement data from a certain type (a or b). Now I want for ever customer the total sum for both types of data a and b.

So, I thought: select the sum, join on a or b and group by a.customer_id, b.customer_id.

Resulting in the following query:

SELECT sum(d.data) as total
FROM data d, ta, tb
WHERE
(d.type LIKE "type_a" AND d.type_id = ta.id) 
OR 
(d.type LIKE "type_b" AND d.type_id = tb.id) 
GROUP BY ta.customer_id, tb.customer_id;

This doesn't get me the proper results...

I tried several approaches, left joins, joining on the customer table and group by customer.id etc. Does anyone have a clue what I'm doing wrong?

Thanx!

ah, you are right :) this is not the actual query but a simplified one. Fixed it. — Steven V
– Steven V, Commented Apr 6, 2011 at 10:07

RichardTheKiwi · Accepted Answer · 2011-04-06 10:10:17Z

5

Your query

SELECT sum(d.data) as total
FROM data d, ta, tb
WHERE
(d.type LIKE "type_a" AND d.type_id = ta.id) 
OR 
(d.type LIKE "type_b" AND d.type_id = tb.id) 
GROUP BY a.customer_id, b.customer_id;

Let's say there is only one record in d, and it is type_a. There are two records in ta and tb each. The record in d matches one of the records in ta on d.type_id=ta.id. Therefore, that combination of (d x ta) allows ANY tb record to remain in the final result. You get an unintended cartesian product.

SELECT x.customer_id, SUM(data) total
FROM
(
    SELECT ta.customer_id, d.data
    FROM data d JOIN ta
       ON (d.type LIKE "type_a" AND d.type_id = ta.id) 
    UNION ALL
    SELECT tb.customer_id, d.data
    FROM data d JOIN tb
       ON (d.type LIKE "type_b" AND d.type_id = tb.id) 
) X
GROUP BY x.customer_id;

answered Apr 6, 2011 at 10:10

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Steven V Over a year ago

Wow that's fast! And indeed exactly the problem and solution. Thanx Richard! My humble greatings from complete the other side of the world :)

Arnaud Bouchot Over a year ago

Thank you very much I was in the same boat an that's the most straight forward solution to this problem I have found so far

Collectives™ on Stack Overflow

MySQL GROUP BY multiple columns from different tables

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related