CODEWITHSUNDEEP
sqlpostgresqlcountgroup-by
Polopollo
Polopollo

Reputation: 377

Include count 0 in my group by request

I have a COUNT + GROUP BY request for postgresql.

SELECT date_trunc('day', created_at) AS "Day" , 
       count(*) AS "No. of actions" 
FROM events 
WHERE created_at > now() - interval '2 weeks' 
  AND "events"."application_id" = 7 
  AND ("what" LIKE 'ACTION%') 
GROUP BY 1 
ORDER BY 1

My request counts the number of "ACTION*" per day on my events table (a log table) in 2weeks for my application with the id 7. But the problem is it doesn't show when there is a Day without any actions recorded.

I know it is because of my WHERE clause, so I tried some stuff with JOIN requests, but nothing gave me the good answer.

Thank you for your help

Upvotes: 0

Views: 278

Answers (2)

Polopollo
Polopollo

Reputation: 377

Ok a friend helped me, here is the answer:

SELECT  "myDates"."DateValue" AS "Day" , 
    (select count(*) from events WHERE   date_trunc('day', "events"."created_at") = "myDates"."DateValue" AND 
    ("events"."application_id" = 4) AND 
    ("events"."what" LIKE 'ACTION%'))  AS "No. of actions"    
FROM    "myDates" 
where ("myDates"."DateValue" > now() - interval '2 weeks') AND ("myDates"."DateValue" < now())

So we need to ask all the date from the MyDates table, and ask the count on the second argument.

Upvotes: 0

paul
paul

Reputation: 22001

Make a date table:

CREATE TABLE "myDates" (
    "DateValue" date NOT NULL
);

INSERT INTO "myDates" ("DateValue") 
select to_date('20000101', 'YYYYMMDD') + s.a as dates from generate_series(0,36524,1) as s(a);

Then left join on it:

SELECT  d.DateValue AS "Day" , 
        count(*) AS "No. of actions"    
FROM    myDates d left join events e on date_trunc('day', "events"."created_at") = d.DateValue
WHERE   created_at > now() - interval '2 weeks' AND 
        "events"."application_id" = 7 AND 
        ("what" LIKE 'ACTION%') 
GROUP BY 1 ORDER BY 1

Upvotes: 2

Related Questions