Using IF output as a variable in MySQL

Question

I have a table alpha has 3 column which id (INT) and time1, time2 (DATETIME)

Now I want to count how many rows in every day from 2017-04-18 to 2017-05-24 and I tried this query but failed

SELECT IF(time1 < time2, time2, time1) AS d, COUNT(DATE(d)) FROM alpha 
WHERE time1 IS NOT NULL AND time2 IS NOT NULL
AND DATE(d) BETWEEN DATE('2017-04-18') AND DATE('2017-05-24')

Data

ID  TIME1                  TIME2
-----------------------------------------------
1, '2017-04-19 04:08:03', '2017-04-19 04:08:03'
2, '2017-04-19 04:08:03', '2017-05-19 04:08:03'
3, '2017-06-19 04:08:03', '2017-04-19 04:08:03'
4, '2017-06-19 04:08:03', '2017-06-20 04:08:03'
5, '2017-05-19 04:08:03', '2017-05-01 04:08:03'

Expected result

DAY         COUNT
---------------------
2017-04-19  1
2017-05-19  2

Can you also share with us some sample data and expected result set? — Giorgos Betsos
– Giorgos Betsos, Commented May 24, 2017 at 5:20
oracle-base.com/articles/misc/lag-lead-analytic-functions#lag — Ajay Kumar
– Ajay Kumar, Commented May 24, 2017 at 5:28

tiomno · Accepted Answer · 2017-05-24 05:31:09Z

One possible solution:

  SELECT DATE(d), COUNT(DATE(d)) FROM (
    SELECT IF(time1 < time2, time2, time1) AS d FROM alpha 
    WHERE time1 IS NOT NULL AND time2 IS NOT NULL
  ) AS t
  WHERE DATE(d) BETWEEN DATE('2017-04-18') AND DATE('2017-05-24')
  GROUP BY DATE(d);

OR which I don't like:

SELECT IF(time1 < time2, time2, time1) AS d, COUNT(DATE(IF(time1 < time2, time2, time1))) FROM alpha 
WHERE time1 IS NOT NULL AND time2 IS NOT NULL
AND DATE(IF(time1 < time2, time2, time1)) BETWEEN DATE('2017-05-13') AND DATE('2017-05-15')
GROUP BY DATE(IF(time1 < time2, time2, time1));

Collectives™ on Stack Overflow

Using IF output as a variable in MySQL

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related