Single query for: Getting ratings from 12 months, but count all

I have the following tables in MySQL 8:

• Restaurant (Id, Name)
• Order (Id, RestaurantId)
• Rating (OrderId, Rating, CreatedAt)

Now I want to calculate rating of a restaurant from past 12 months but also need to get count of all, so it will be shown on front-end like “4.7 stars (120 reviews)”, here 4.7 stars are average of 12 months rating, but 120 is count of all ratings. Here’s the query which I tried, but count shows 1 with each row:

SELECT COUNT(r.Id), r.Rating FROM `Order` o
JOIN `rating` r ON r.OrderId = o.Id
WHERE o.RestaurantId = 1 AND r.CreatedAt > now() - INTERVAL 12 month
GROUP BY r.Id

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

SELECT o.RestaurantId,
       COUNT(*) AS total, 
       AVG(CASE WHEN r.CreatedAt > now() - INTERVAL 12 month 
                THEN r.Rating 
          END) AS rating_12_months
FROM `Order` o
JOIN `rating` r ON r.OrderId = o.Id
GROUP BY o.RestaurantId

Method 2

Use conditional aggregation

SELECT re.Name,                                              -- restaurant
       COUNT(*) total_count,                                 -- total count for it, without date filtering
       AVG(CASE WHEN ra.CreatedAt > NOW() - INTERVAL 1 YEAR  -- average rating for it with date filtering,
                THEN ra.Rating
                END) average_rating_last_year
FROM Restaurant re
JOIN Order or ON re.id = or.RestaurantId
JOIN Rating ra ON or.id = ra.OrderId
GROUP BY re.Name

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0