MySQL Columns Into Rows [duplicate]

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

Giorgos Betsos · Accepted Answer · 2016-03-22 14:41:51Z

0

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

answered Mar 22, 2016 at 14:41

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

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

Collectives™ on Stack Overflow

MySQL Columns Into Rows [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related