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

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

CodePudding user response：

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

CodePudding user response：

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