CODEWITHSUNDEEP
sqlpostgresql
elektruver
elektruver

Reputation: 243

PostgreSQL: How to count requests by date if there is no request for a date in a table then 0?

I have table, like this:

| id | datetime | resource |
|:---|---------:|:--------:|
| 1  |2019-12-18| /v1      |
| 2  |2019-12-18| /v1      |
| 3  |2019-12-18| /v2      |
| 4  |2019-12-27| /v3      |

I need count resource per day. And I can't understand how to build query for getting like this:

| id | datetime | resource | count |
|:---|---------:|:--------:|:-----:|
| 1  |2019-12-18| /v1      | 2     |
| 2  |2019-12-18| /v2      | 1     |
| 3  |2019-12-18| /v3      | 0     |
| 4  |2019-12-27| /v3      | 1     |
| 5  |2019-12-27| /v1      | 0     |
| 6  |2019-12-27| /v2      | 0     |

Upvotes: 1

Views: 92

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270623

Use a cross join to generate the rows. Then use a left join to bring in the original values and aggregation:

select row_number() over (order by r.resource, d.datetime) as seqnum,
       d.datetime, r.resource,
       count(t.id) as cnt
from (select distinct datetime from t) d cross join
     (select distinct resource from t) r left join
     t
     on d.datetime = t.datetime and
        r.resource = t.resource
group by d.datetime, r.resource
order by seqnum;

Here is a db<>fiddle.

Upvotes: 0

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65363

One option would be using CROSS JOIN to determine cross product relation among resource and datetime columns and then LEFT JOIN to combine with subquery in which there are grouped resource and datetime columns containing aggregation :

SELECT row_number() over (ORDER BY datetime, count DESC, resource) AS ID,
       q.* 
  FROM
  ( SELECT t3.datetime, t3.resource, COALESCE(t4.count,0) AS count 
      FROM
      (
       (SELECT distinct resource FROM tab) t1
         CROSS JOIN (SELECT distinct datetime FROM tab) t2 ) t3
      LEFT JOIN
     (  
      SELECT datetime, resource,count(datetime) as count
        FROM tab
       GROUP BY datetime, resource 
     ) t4
     ON t4.datetime = t3.datetime
    AND t4.resource = t3.resource
  ) q;

Demo

Upvotes: 2

Pranav MS
Pranav MS

Reputation: 2296

Please try like below. IE. Group by with datetime and resource then take the count for the same you will get the Output . Please try this.

SELECT datetime, resource,count(id) AS count
    FROM public.test GROUP BY datetime,resource;

Output

datetime    resource    count
18-12-2019  /v2         1
18-12-2019  /v1         2
27-12-2019  /v3         1

Upvotes: 0

Related Questions