Reputation: 3
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
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.date_trunc()
produces a date column, on the first day of the month.Upvotes: 1
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