CODEWITHSUNDEEP
mysqlsql
Robinxon
Robinxon

Reputation: 11

SQL count values in different columns with date range

I know how to count values in different columns and with date ranges, but not when I have to do both things in the same time.

I have table with this example values: 

   DATE    | Number1 | Number2 | Number3 | Number4 | Number5 | Number6  
2017-08-05 |   01    |   02    |   03    |   04    |    05   |   06
2017-08-04 |   02    |   03    |   04    |   05    |    06   |   07
2017-08-03 |   03    |   04    |   05    |   06    |    07   |   08
2017-08-02 |   04    |   05    |   06    |   07    |    08   |   09
2017-08-01 |   05    |   06    |   07    |   08    |    09   |   10

And now i want to have result like this for choosen data range (ex. from 2017-08-02 to 2017-08-03): 

Number | occurrences
  03   |     1
  04   |     2
  05   |     2
  06   |     2
  07   |     2
  08   |     2
  09   |     1

How to do this in one SQL query?

Upvotes: 1

Views: 165

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175556

You could use subquery and then normal GROUP BY:

SELECT num, COUNT(*) AS Occurences
FROM (
  SELECT date_col, Number1 AS num
  FROM table_name
  UNION ALL
  SELECT date_col, Number2 
  FROM table_name
  ...
) AS sub
-- WHERE date_col BETWEEN ... AND  ... --specific range
GROUP BY num
ORDER BY num;

Anyway you should rethink your schema design.

Upvotes: 1

Related Questions