CODEWITHSUNDEEP
sqlpostgresql
Skypies
Skypies

Reputation: 3

How to GROUP BY column into months and COUNT per month?

I am trying to return number of rental orders each store as fulfilled per month. I want to show four columns; month, year, store ID, COUNT of rentals (per month). I have am able to return the month, year and store ID columns but the COUNT of rentals does not COUNT by month: 

SELECT 
DATE_PART('month', r.rental_date) Rental_month, 
DATE_PART('year', r.rental_date) Rental_year, 
s.store_id, 
COUNT(r.rental_date) count_rentals 
FROM store s
JOIN staff staf
ON s.store_id = staf.store_id
JOIN payment pay
ON staf.staff_id = pay.staff_id
JOIN rental r
ON pay.rental_id = r.rental_id
GROUP BY r.rental_date, s.store_id

My output: My output

My desired output would look like this: desired output

Upvotes: 0

Views: 480

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

Instead of using date_part(), I would recommend date_trunc():

SELECT DATE_TRUNC('month', r.rental_date) as yyyymm, 
       st.store_id, 
       COUNT(*) as count_rentals 
FROM staff st JOIN
     payment p
     ON st.staff_id = p.staff_id JOIN
     rental r
     ON p.rental_id = r.rental_id
GROUP BY yyyymm, st.store_id;

Notes:

  • store is not needed in the query. The store_id is stored on the staff table.
  • In Postgres, you can aggregate by a column alias.
  • date_trunc() produces a date column, on the first day of the month.

Upvotes: 1

FizzBuzz
FizzBuzz

Reputation: 693

You need to actually group by the month and year instead of the full date. Change your GROUP BY to -

GROUP BY  
       DATE_PART('month', r.rental_date),
       DATE_PART('year', r.rental_date),
       s.store_id,

Upvotes: 0

Related Questions