CODEWITHSUNDEEP
sqlpostgresql
Americo
Americo

Reputation: 919

POSTGRESQL - Strategy on grouping a query

I have this query:

SELECT 
 u.creationtime::date as date,
 d.driver_statuses_city_id as id,
 u.usersid as applied
FROM 
users u 
JOIN 
driver_status d 
ON 
u.usersid = d.driver_id
WHERE 
(u.role = 'partner' or u.role = 'driver')
AND d.driver_statuses_city_id in 
(SELECT id FROM driver_statuses_city
WHERE city_id in ({{city_ids}}))
and u.creationtime::date > '2013-09-01'

Which outputs:

date          id      applied
2013-09-03   1121     2443122
2013-09-03   1122     2737622
2013-09-03   1123     2754122
2013-09-03   1122     2733422
etc           etc       etc

I want to group each id together by date. So that my output would look more like:

date                id                count(applied)
2013-09-03          1121                  1
2013-09-03          1122                  2
2013-09-03          1123                  1

But i am unsure of how to best display these. Would I be best inclined to use some sort of "case" function?

Upvotes: 0

Views: 42

Answers (1)

Arpit Agrawal
Arpit Agrawal

Reputation: 891

I do not have your complete table structure but from what I can make out, it should be something like this: 

SELECT 
 u.creationtime::date as date,
 d.driver_statuses_city_id as id,
 count(u.usersid) as applied
FROM 
users u 
JOIN 
driver_status d 
ON 
u.usersid = d.driver_id
WHERE 
(u.role = 'partner' or u.role = 'driver')
AND d.driver_statuses_city_id in 
(SELECT id FROM driver_statuses_city
WHERE city_id in ({{city_ids}}))
and u.creationtime::date > '2013-09-01'
group by date, id

Upvotes: 1

Related Questions