Vladimir Lebedev Vladimir Lebedev - 1 year ago 42
SQL Question

Count in each row the number of second column

Here is answer to request

enter image description here

The question is how to count by each selected_date e.x:


  1. 2012-02-10: 1

  2. 2012-02-15: 0

  3. 2012-02-14: 3

  4. 2012-02-11: 0



How to make this request

Here is the request to get above answer

select selected_date, date1 from
(select selected_date from
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between '2012-02-10' and '2012-02-15' ) vv left join clicker on clicker.date1=vv.selected_date

Answer Source

This might work:

SELECT selected_date, SUM(CASE WHEN date1 IS NULL THEN 0 ELSE 1 END) FROM table
GROUP BY selected_date