CODEWITHSUNDEEP
sqlpostgresql
DBE7
DBE7

Reputation: 816

PostgreSQL: trying to retrieve yesterday's count

I am trying to get the number of bookings from yesterday. Here is my code:

SELECT COUNT(distinct be.booking_id) AS "Number of Bookings Approved Today",
       TO_CHAR(be.created_at, 'yyyy-mm-dd') as "id"
FROM booking_events be
WHERE be.event IN ('approve', 'instant_approve') AND
      be.created_at > TIMESTAMP 'yesterday' AND
      be.created_at < TIMESTAMP 'today'
GROUP BY be.created_at;

Right now, above code wont let me view the actual count of the number of bookings from yesterday. Rather it retrieves all the rows like this:

Date           # of bookings
<yesterday's date>       1
<yesterday's date>       1
<yesterday's date>       1
<yesterday's date>       1
<yesterday's date>       1
<yesterday's date>       1
<yesterday's date>       1

I want it to return the total count for yesterday's date like this:

Date,          # of bookings
<yesterday's date>       7

Hope somebody is able to help me out. It's probably an easy fix, but I can't wrap my brain around it!

Upvotes: 0

Views: 38

Answers (2)

DBE7
DBE7

Reputation: 816

I realised that I just had to GROUP BY 2. @jpw commented the reasoning behind it!

Upvotes: 0

jpw
jpw

Reputation: 44911

Perhaps be.created_at is a timestamp with a time part?

Try changing your group by clause to TO_CHAR(be.created_at, 'yyyy-mm-dd') so that the grouping is done by the date part only. Or you could use group by with an ordinal value indicating the column from the select statement to group by: group by 2 (for the second column - the date in your sample query).

Upvotes: 1

Related Questions