Help to make a SQL query to select id, sorted by date from 01/01/2018 to 04/01/2018? In my login table, the type is varchar.

users id login ----------------- 100 04.01.2018 101 01.01.2018 180 02.01.2018 103 03.01.2018 104 02.03.2018 205 03.01.2018 203 05.04.2018

Submission Required:

 01.01.2018 02.01.2018 03.01.2018 04.01.2018 1 1 2 1

I made such a request, but it does not work

 SELECT COUNT(DISTINCT id) as '01.01.2018' FROM users where login = '01.01.2018' union SELECT COUNT(DISTINCT id) as '02.01.2018' FROM users where login = '02.01.2018'; union SELECT COUNT(DISTINCT id) as '03.01.2018' FROM users where login = '03.01.2018'; union SELECT COUNT(DISTINCT id) as '06.04.2018' FROM users where login = '04.01.2018';

I would be grateful for the help!

  • SELECT SUM(login='2018-01-01') `01.01.2018`, ... - Akina
  • Thank! Such code really earned. SELECT SUM (first_login_time = '01 .01.2018 ') 01.01.2018 , SUM (first_login_time = '02 .01.2018') 01/01/2018, SUM (first_login_time = '03 .01.2018') 03.01.2018 , SUM (first_login_time = '04 .01. 2018 ') 04.01.2018 from users; Are there any ways to further simplify, for example, specifying the date interval? The fact is that sampling can be for a large number of days. To not manually write every date - Elisha Gorkov
  • To manually not write, you should use GROUP BY . SELECT COUNT(*), login FROM users GROUP BY(login) . Get a different look, but will not suffer from this nonsense - ArchDemon
  • Are there any ways to further simplify, for example, specifying the date interval? Simplify - no. This will definitely complicate the code. MySQL does not know about PIVOT (it is painfully non-relational), but yes, it is formally possible by writing acc. stored procedure. Although it is more correct to perform the transformation already on the client by means of the component of building reports. - Akina

0