How to make SQL query to select unique id and range them in columns by date?

Question

everybody! Could you help me to make SQL query to select unique id and range them in columns by date from 01.01.2018 until 04.01.2018? In my table login has varchar type.

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

Required view

01.01.2018   02.01.2018   03.01.2018   04.01.2018  
1           1            2             1

I make such query, 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 really appreciate any help and directions!

It is better not to write every date in query, but how use time period ? — Елисей Горьков, Aug 30 '18 at 18:42

score 0 · Answer 1 · answered Aug 30 '18 at 18:42

You can do conditional aggregation :

SELECT COUNT(DISTINCT CASE WEHN login = '01.01.2018' THEN id END) AS '01.01.2018',
       COUNT(DISTINCT CASE WEHN login = '02.01.2018' THEN id END) AS '02.01.2018',
       COUNT(DISTINCT CASE WEHN login = '03.01.2018' THEN id END) AS '01.01.2018',
       COUNT(DISTINCT CASE WEHN login = '04.01.2018' THEN id END) AS '01.01.2018'
FROM users;

However, your required output suggests me :

SELECT SUM( login = '01.01.2018' ) AS '01.01.2018',
       SUM( login = '02.01.2018' ) AS '02.01.2018',
       SUM( login = '03.01.2018' ) AS '03.01.2018',
       SUM( login = '04.01.2018' ) AS '04.01.2018'
FROM users;

Thanks, but is there any way to do this without wriring every date? May be set a period? Because in my task I need to select data from large time period, — Елисей Горьков, Aug 30 '18 at 18:57

How to make SQL query to select unique id and range them in columns by date?

1 Answers1