# MySQL count multiple columns and sum the total occurrence

I have the following table, say, MyDRAW ...

``````  DRAWNO    PRIZENO FIRSTD  SECONDD THIRDD  FOURTHD
M123/14     8458    8   4   5   8
M123/14     7214    7   2   1   4
M123/14     1640    1   6   4   0
M123/14     2103    2   1   0   3
M123/14     2557    2   5   5   7
M123/14     2975    2   9   7   5
M123/14     3767    3   7   6   7
M123/14     4409    4   4   0   9
M123/14     4887    4   8   8   7
M123/14     5844    5   8   4   4
``````

I would like to count and sum them UP how many times of occurrence of digit from 0 to 9 (0,1,2,3,4,5,6,7,8,9) for 4 different differenct columns, which is FIRSTD, SECONDD, THIRDD, FORTHD ...

Expected result is ..

``````DRAWNO   [D0][D1][D2][D3][D4][D5][D6][D7][D8][D9]
M123/14  3   3   4   2   8   5   2   6   5   2
``````

I managed to know how to select one Columns, and Question how to select all columns () and sum them up into TOTAL occurrence of digit 0 to digit 9?

..........

``````SELECT
DRAWNO,
FIRSTD,
COUNT(FIRSTD) AS COUNT1
FROM t4d
GROUP BY DRAWNO, FIRSTD
ORDER BY DRAWNO, FIRSTD
``````

..........

Thanks.

This is a query with an idea like in MySQL count multiple columns and sum the total occurrence

``````SELECT
drawno,
SUM(CASE WHEN FIRST = 0 THEN 1 ELSE 0 END) zero,
SUM(CASE WHEN FIRST = 1 THEN 1 ELSE 0 END) one,
SUM(CASE WHEN FIRST = 2 THEN 1 ELSE 0 END) two,
SUM(CASE WHEN FIRST = 3 THEN 1 ELSE 0 END) three,
SUM(CASE WHEN FIRST = 4 THEN 1 ELSE 0 END) four,
SUM(CASE WHEN FIRST = 5 THEN 1 ELSE 0 END) five,
SUM(CASE WHEN FIRST = 6 THEN 1 ELSE 0 END) six,
SUM(CASE WHEN FIRST = 7 THEN 1 ELSE 0 END) seven,
SUM(CASE WHEN FIRST = 8 THEN 1 ELSE 0 END) eight,
SUM(CASE WHEN FIRST = 9 THEN 1 ELSE 0 END) nine
FROM
(
SELECT
first
,drawno
FROM table1
UNION ALL
SELECT
second
,drawno
FROM table1
UNION ALL
SELECT
third
,drawno
FROM table1
UNION ALL
SELECT
fourth
,drawno
FROM table1
) AS t GROUP BY drawno;
``````

Two queries, this and from first answer: http://sqlfiddle.com/#!2/2eb93d/2

More verbose than a Dostoevsky novel,but it works

``````    SELECT drawno,SUM(zero)zero,SUM(one)one,SUM(two)two,SUM(three)three,SUM(four)four,SUM(five)five,SUM(six)six,SUM(seven)seven,SUM(eight)eight,SUM(nine)nine
FROM
(SELECT drawno,
SUM(CASE WHEN first=0  THEN 1 ELSE 0 END) zero,
SUM(CASE WHEN first=1  THEN 1 ELSE 0 END) one,
SUM(CASE WHEN first=2  THEN 1 ELSE 0 END) two,
SUM(CASE WHEN first=3  THEN 1 ELSE 0 END) three,
SUM(CASE WHEN first=4  THEN 1 ELSE 0 END) four,
SUM(CASE WHEN first=5  THEN 1 ELSE 0 END) five,
SUM(CASE WHEN first=6  THEN 1 ELSE 0 END) six,
SUM(CASE WHEN first=7  THEN 1 ELSE 0 END) seven,
SUM(CASE WHEN first=8  THEN 1 ELSE 0 END) eight,
SUM(CASE WHEN first=9  THEN 1 ELSE 0 END) nine
FROM table1
GROUP BY drawno
UNION
SELECT drawno,
SUM(CASE WHEN second=0  THEN 1 ELSE 0 END) zero,
SUM(CASE WHEN second=1  THEN 1 ELSE 0 END) one,
SUM(CASE WHEN second=2  THEN 1 ELSE 0 END) two,
SUM(CASE WHEN second=3  THEN 1 ELSE 0 END) three,
SUM(CASE WHEN second=4  THEN 1 ELSE 0 END) four,
SUM(CASE WHEN second=5  THEN 1 ELSE 0 END) five,
SUM(CASE WHEN second=6  THEN 1 ELSE 0 END) six,
SUM(CASE WHEN second=7  THEN 1 ELSE 0 END) seven,
SUM(CASE WHEN second=8  THEN 1 ELSE 0 END) eight,
SUM(CASE WHEN second=9  THEN 1 ELSE 0 END) nine
FROM table1
GROUP BY drawno
UNION
SELECT drawno,
SUM(CASE WHEN third=0  THEN 1 ELSE 0 END) zero,
SUM(CASE WHEN third=1  THEN 1 ELSE 0 END) one,
SUM(CASE WHEN third=2  THEN 1 ELSE 0 END) two,
SUM(CASE WHEN third=3  THEN 1 ELSE 0 END) three,
SUM(CASE WHEN third=4  THEN 1 ELSE 0 END) four,
SUM(CASE WHEN third=5  THEN 1 ELSE 0 END) five,
SUM(CASE WHEN third=6  THEN 1 ELSE 0 END) six,
SUM(CASE WHEN third=7  THEN 1 ELSE 0 END) seven,
SUM(CASE WHEN third=8  THEN 1 ELSE 0 END) eight,
SUM(CASE WHEN third=9  THEN 1 ELSE 0 END) nine
FROM table1
GROUP BY drawno
UNION
SELECT drawno,
SUM(CASE WHEN fourth=0  THEN 1 ELSE 0 END) zero,
SUM(CASE WHEN fourth=1  THEN 1 ELSE 0 END) one,
SUM(CASE WHEN fourth=2  THEN 1 ELSE 0 END) two,
SUM(CASE WHEN fourth=3  THEN 1 ELSE 0 END) three,
SUM(CASE WHEN fourth=4  THEN 1 ELSE 0 END) four,
SUM(CASE WHEN fourth=5  THEN 1 ELSE 0 END) five,
SUM(CASE WHEN fourth=6  THEN 1 ELSE 0 END) six,
SUM(CASE WHEN fourth=7  THEN 1 ELSE 0 END) seven,
SUM(CASE WHEN fourth=8  THEN 1 ELSE 0 END) eight,
SUM(CASE WHEN fourth=9  THEN 1 ELSE 0 END) nine
FROM table1
GROUP BY drawno)x
GROUP BY drawno
``````

SQL Fiddle

If you dont care about drawno,that is, count all the numbers in the table:

SQL Fiddle

• Thanks Mihai ... Your answer is great .. I have make similar, but yours is better I think :) SELECT TheDRAWDATE, TheDRAWNO, Number, COUNT(*) AS OCCURRENCE FROM( SELECT DRAWDATE As TheDRAWDATE, DRAWNO AS TheDRAWNO, FIRSTD AS Number FROM t4d UNION ALL SELECT DRAWDATE As TheDRAWDATE, DRAWNO AS TheDRAWNO, SECONDD AS Number FROM t4d UNION ALL SELECT DRAWDATE As TheDRAWDATE, DRAWNO AS TheDRAWNO, THIRDD AS Number FROM t4d UNION ALL SELECT DRAWDATE As TheDRAWDATE, DRAWNO AS TheDRAWNO, FOURTHD AS Number FROM t4d ) AS Baseview GROUP BY TheDRAWNO, Number Jan 25, 2014 at 17:42
• You could improve (reduce the length of the query) by first using `UNION ALL` to union the 4 columns, and then `GROUP BY` with the ten `SUM(CASE ...)` Jan 25, 2014 at 22:33
• I laughed at "More verbose than a Dostoevsky novel" :) Dec 4, 2014 at 14:51