Luca Fancello Luca Fancello - 5 months ago 11
SQL Question

How can I count the number of

On our Postgres Database we would like to count the number of bookings we have per day since the beginning of time.

For Now we have the following query:

SELECT
to_char("domain".bookings.created_at,'dy') AS "Name of the day",
COUNT ("domain".bookings."id") AS "Number of bookings"
FROM
"domain".bookings
GROUP BY
to_char("domain".bookings.created_at,'dy'),
"domain".bookings."id"
ORDER BY
"domain".bookings."id"


Unfortunately it is returning the following results:

tue 1
wed 1
wed 1
sun 1
sat 1
thu 1
wed 1
tue 1


Instead of the total per day.

Is there a way to change the formula?

Thank you!

Luca

Answer

It's the group by that's causing your problem. You don't want to group by your aggregated fields

SELECT 
   to_char("domain".bookings.created_at,'dy') AS "Name of the day",
   COUNT ("domain".bookings."id") AS "Number of bookings"
FROM
   "domain".bookings
GROUP BY
   to_char("domain".bookings.created_at,'dy')
ORDER BY
   to_char("domain".bookings.created_at,'dy')