0

I want to get a list of groups and the total number of badges number they've ordered:

SELECT g.id, g.name, SUM(oi.qty_order) AS badge_count
FROM groups g
INNER JOIN users u ON u.group_id = g.id
INNER JOIN orders o ON o.user_id = u.id
INNER JOIN customisations c ON c.order_id = o.id
INNER JOIN customisation_items ci ON ci.customisation_id = c.id
INNER JOIN order_items oi ON oi.order_id = o.id
WHERE c.has_badge = 1
AND o.created_at > '2021-01-01'
GROUP BY c.id
ORDER BY c.id DESC

The problem with the results from the above query is that it is returning the correct customisations BUT, SUM(oi.qty_order) AS badge_count is including order_items that don't contain a badge (c.has_badge = 1).

xylar
  • 7,433
  • 17
  • 55
  • 100
  • What makes you think it's including those rows? – Barmar Feb 01 '22 at 18:02
  • If there are multiple customization items, you're duplicating the order item for each customization item, and that will multiply the quantity. – Barmar Feb 01 '22 at 18:04

0 Answers0