Vladimir Lebedev Vladimir Lebedev - 21 days ago 5
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

This might work:

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