MySQL count multiple columns and sum the total occurrence

Question

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.

Answer 1

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

Answer 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

• cybervista: 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
• ypercubeᵀᴹ: 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 ...)
• Mladen Uzelac: I laughed at "More verbose than a Dostoevsky novel" :)