JOINS AND SUM()'s, joining table to itself...

[shadedecho]

OK... I am getting confused with how to JOIN a table to itself (1 or more times) to get "horizontally" different SUM()'s on a particular field...

mytable:

+-----+-----+
|  A  |  B  |
+-----+-----+
|  1  |  13 |
|  1  |  3  |
|  2  |  6  |
|  1  |  1  |
|  3  |  5  |
|  2  |  5  |
+-----+-----+

If I do:
SELECT `A`, SUM(`B`) AS 'Btotal' FROM `mytable` GROUP BY `A`

I get

+-----+----------+
|  A  |  Btotal  |
+-----+----------+
|  1  |    17    |
|  2  |    11    |
|  3  |    5     |
+-----+----------+

BUT, what I would like is to have those same totals reported to me horizontally in one row... like:

+----------+----------+----------+
|  1total  |  2total  |  3total  |
+----------+----------+----------+
|    17    |    11    |    5     |
+----------+----------+----------+

My first thought is that this has to be done with joins... like joining the table to itself another one 3 times, each time with a different ON clause... so, first:

Atable
+-----+
|  A  |
+-----+
|  1  |
|  2  |
|  3  |
+-----+

If I do a LEFT JOIN from Atable to mytable, ON A=A, like:

SELECT SUM(mytab.`B`) AS 'Btotal' FROM `Atable` atab LEFT JOIN `mytable` mytab ON atab.`A` = mytab.`A` GROUP BY atab.`A`

That would give me the three row-totals again, this time with a JOIN involved, so the SUM works correct over that one join...

So, to get it to put them all in a row, I figured I could just JOIN mytable to Atable 3 times, each time with a different ON condition...


SELECT SUM(mytable1.`B`) AS '1total',
SUM(mytable2.`B`) AS '2total',
SUM(mytable3.`B`) AS '3total'
FROM `Atable` Atable1
LEFT JOIN `mytable` mytable1 ON mytable1.`A` = 1 AND mytable1.`A` = Atable1.`A`
LEFT JOIN `mytable` mytable2 ON mytable2.`A` = 2 AND mytable2.`A` = Atable1.`A`
LEFT JOIN `mytable` mytable3 ON mytable3.`A` = 3 AND mytable3.`A` = Atable1.`A`
GROUP BY Atable1.`A`

I thought the temp table created after these joins but before the sum'ing would look something like:

+-----+------+------+------+
|  1  |  13  | null | null |
|  1  |  3   | null | null |
|  2  | null |  6   | null |
|  1  |  1   | null | null |
|  3  | null | null |  5   |
|  2  | null |  5   | null |
+-----+------+------+------+

And the result I get is closer to what I want, but still not one row in the result set...

+-----+-----+-----+
|  17 |  0  |  0  |
|  0  |  11 |  0  |
|  0  |  0  |  5  |
+-----+-----+-----+

Clearly, the GROUP BY is part of the problem, cause its creating the 3 rows. So, I thought maybe if I removed the GROUP BY it would bring it all down to one row, which it did, but this time the SUM'ing didn't work right at all. It added all 3 totals together, and put that in all 3 fields:

+-----+-----+-----+
|  33 |  33 |  33 |
+-----+-----+-----+

Lastly, I thought maybe I could just not have the "Atable" involved (notice below the aliases are named the same for consistency between the two queries, but now it refers to `mytable` all 4 times), and just join the table to itself 4 times (since the table itself has the correct `A` values in it already, like:

SELECT SUM(mytable1.`B`) AS '1total',
SUM(mytable2.`B`) AS '2total',
SUM(mytable3.`B`) AS '3total'
FROM `mytable` Atable1
LEFT JOIN `mytable` mytable1 ON mytable1.`A` = 1 AND mytable1.`A` = Atable1.`A`
LEFT JOIN `mytable` mytable2 ON mytable2.`A` = 2 AND mytable2.`A` = Atable1.`A`
LEFT JOIN `mytable` mytable3 ON mytable3.`A` = 3 AND mytable3.`A` = Atable1.`A`
GROUP BY Atable1.`A`

This produced nowhere near what I was wanting... the first 2 rows of the result set, the SUM was tripled, and the last row of the result set, the SUM was quadrupled. this makes no sense to me whatsoever.

+-----+-----+-----+
|  51 |  0  |  0  |
|  0  |  33 |  0  |
|  0  |  0  |  20 |
+-----+-----+-----+

And without the GROUP BY in that statement, it adds all 3 together, then triples that SUM for the first 2 fields and quadruples it for the third field:

+-----+-----+-----+
|  99 |  99 | 132 |
+-----+-----+-----+

<insert growl here> Any thoughts?

 

[swampBoogie]

select A,
sum(case when A = 1 then B else 0 end),
sum(case when A = 2 then B else 0 end),
sum(case when A = 3 then B else 0 end)
from t
where A in (1,2,3)

 

[shadedecho]

swampBoogie, that reports to me an SQL error, saying that mysql reports back:

&quot;Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause&quot;

So, adding in a &quot;GROUP BY&quot; does get me back to my 3-row matrix of totals, as did my first attempt at the JOINs in my original post... but i need to compress those 3 rows into one row.

that's a smart way to think about it though, I never realized I could but conditionals inside the &quot;SUM&quot; statment... now wondering if conditionals would work combined with my JOIN and no group, which was triple/quadrupling... gonna go try it.

 

[swampBoogie]

Sorry, A should not be part of the query

select sum(case when A = 1 then B else 0 end) as one,
sum(case when A = 2 then B else 0 end) as two,
sum(case when A = 3 then B else 0 end) as three
from t
where A in (1,2,3)

 

[shadedecho]

OK!!!

so, when I am using the external table (&quot;Atable&quot, and put in the CASE statements into the SUM() function, and do 3 joins of &quot;mytable&quot; to Atable, it does give me the DESIRED one-row result set, correctly sum'd.

for my application, it is really needed that it not require the &quot;A&quot; values in another table, it would be nice to just JOIN it to itself one more time, instead of joining to another table which doesn't actually exist. (though if push comes to shove i could always just select DISTINCT INTO a temp table and then join to that temp table).

FYI, when i try the CASE-solution in the SUM()'s with the mytable table JOINED 4 times), i get the one row result, but the sums are tripled and quadrupled as before.

anyone know of a way to get around having to have that external table in there... here's the query that works with the external distinct-values table:

SELECT SUM(CASE WHEN Atable1.`A` = 1 THEN mytable1.`B` ELSE 0 END) AS '1total',
SUM(CASE WHEN Atable1.`A` = 2 THEN mytable2.`B` ELSE 0 END) AS '2total',
SUM(CASE WHEN Atable1.`A` = 3 THEN mytable3.`B` ELSE 0 END) AS '3total'
FROM `Atable` Atable1
LEFT JOIN `mytable` mytable1 ON mytable1.`A` = 1 AND mytable1.`A` = Atable1.`A`
LEFT JOIN `mytable` mytable2 ON mytable2.`A` = 2 AND mytable2.`A` = Atable1.`A`
LEFT JOIN `mytable` mytable3 ON mytable3.`A` = 3 AND mytable3.`A` = Atable1.`A`

 

[shadedecho]

OK... swampBoogie is awesome! that works... I had one additional note to his solution however...

in my example (which is obviously simplified from the real world app i am working on) I had no need for selecting out other rows into the result set... so, this works fine... but as is shown in his first post, with the &quot;A&quot; being selected, mysql would then require a valid GROUP BY clause. When I first tried his solution, i just naturally did the &quot;GROUP BY A&quot;... which gave me 3 rows... now I re-tried it, but this time with GROUP BY '' (an empty string)... I get my ONE row, correctly sum'd, and the extra field selected out, which (curiously) takes the value of &quot;1&quot; for A (I expected it to be &quot;3&quot;, the last value A probably holds in the GROUP'ing/SUM'ing process). YEA!

Why is this necessary, you must be asking?? well, I am going to be joining these sum RESULTs to another table, and need to see the key that joins the related &quot;child&quot; records which are being sum'd to their parent record, whose primary key is the field in question needing to be selected.