MySQL count multiple columns and sum the total occurrence

countdatabase-designMySQL

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.

Best Answer

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

Related Solutions

MySQL – SUM with Multiple GROUP BY on Same Table

I'm more of a SQL Server guy, but here is a stab to get you motivated. You could use the CASE expression to help SUM() orders by certain order types.

For example:

SELECT
    idclient AS [Client ID]
   ,COUNT(value) AS [Total Orders]
   ,SUM(value) AS [Total Order Value]
   ,SUM( CASE WHEN type1 = 'some type' THEN value ELSE 0 END) AS [Order Combo 1]
   ,SUM( CASE WHEN type1 = 'some type' AND type2 = 'another type' THEN value ELSE 0 END) [Order Combo 2]
   ,SUM( CASE WHEN type1 = 'some type' AND type2 = 'another type' AND type3 = 'another type' THEN value ELSE 0 END) [Order Combo 3]
FROM 'test'.'orders'
GROUP BY idclient

This will group the orders on [idclient] and will SUM([value]) the instances where certain order records meet the following conditions or combinations you specified.

So, if a customer has an order where [type1] was Apples and [type2] was Oranges, you can SUM([value]) that was 1 under a new column called something like [Apple/Orange Orders].

The result can also include additional combinations for the same record in multiple fields/columns. If that is a problem, just move the CASE to the GROUP BY to break each combination into a separate record in the the result set.

Hope this helps!

MySQL – Using COUNT(), GROUP BY, and GROUP_CONCAT() Together

To create the result you wanted, I used a subquery and used GROUP_CONCAT() from there.

create table test
(
  id int,
  event varchar(3)
  );
  insert into test
  values
  (22,'a'),(23,'bb'),(24,'bb'),(25,'ccc'),(26,'ccc'),(27,'ccc')


select group_concat(total)
from
(
SELECT COUNT(event) AS total FROM test GROUP BY event
  )a

+---------------------+
|group_concat(total)  |
+---------------------+
|1,2,3                |
+---------------------+

DB Fiddle

Best Answer

Related Solutions

MySQL – SUM with Multiple GROUP BY on Same Table

MySQL – Using COUNT(), GROUP BY, and GROUP_CONCAT() Together

Related Question