1

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.

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

0

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 – cybervista Jan 25 '14 at 17:42
  • 1
    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 ...) – ypercubeᵀᴹ Jan 25 '14 at 22:33
  • 2
    I laughed at "More verbose than a Dostoevsky novel" :) – Mladen Uzelac Dec 4 '14 at 14:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.