MySQL - Pivot columns into rows

Question

I'm trying to sum columns in a table and then return each column and value as a separate row, but only if the sum is > 0. Here's an example table

…   stuff   widget  toast   …
-   -----   ------  -----   -
…   0       0       1       …
…   1       0       3       …
…   2       0       1       …
…   0       0       1       …
…   0       0       0       …

Summing the columns is easy enough

select sum(stuff) as stuff, sum(widget) as widget, sum(toast) as toast from table

Which produces this output

stuff   widget  toast
-----   ------  -----
3       0       6

But what I actually want to end up with is this (notice that widget is missing since the sum is 0)

thing   count
----    -----
stuff   3
toast   6

Any advice?

C3roe · Accepted Answer · 2014-02-13 22:46:11Z

2

Here you go:

SELECT 'stuff' as thing, sum(stuff) `count` FROM foo HAVING `count` > 0
UNION
SELECT 'widget', sum(widget) s FROM foo HAVING s > 0
UNION
SELECT 'toast', sum(TOAST) t FROM foo HAVING t > 0

http://sqlfiddle.com/#!2/b586f/7

Aliases in first SELECT determine the column names of the result, aliases in following SELECTs don’t matter, therefor I used short ones.

And HAVING the respective sum > 0 eliminates the widget column in the result as requested.

Only feasible for that small amount of original columns though – for a higher number of columns you probably wouldn’t want to do that.

edited Feb 13, 2014 at 22:46

answered Feb 13, 2014 at 22:36

C3roe

97.7k15 gold badges100 silver badges172 bronze badges

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

Collectives™ on Stack Overflow

MySQL - Pivot columns into rows

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related