MySQL Columns Into Rows

Question

I am trying to create a list based on columns in my table and sum the values. I have the following table

id | box1 | box2 | box3
------------------------
111 | 1 | 0 | 1
111 | 0 | 1 | 1
111 | 1 | 1 | 1
111 | 0 | 0 | 1

I need the result to be like this

column | sum
------------
box1 | 2
box2 | 2
box3 | 4

I tried to to a union all and select each column but I was able to get the correct column title. I was able to get the sum but I couldn't figure out how to get the column name.

SELECT SUM(box1) as box1Count FROMqueue
UNION ALL
SELECT SUM(box2) as box2Count FROM queue
UNION ALL
SELECT SUM(box3) as box3Count FROM queue

Answer 1

You can hard-code the column value in the SELECT clause:

SELECT 'box1' AS col, SUM(box1) as box1Count FROM queue
UNION ALL
SELECT 'box2', SUM(box2) as box2Count FROM queue
UNION ALL
SELECT 'box3', SUM(box3) as box3Count FROM queue