MySQL: left join only one row and sum

Question

It's possible left join only one row without sub query?

I need to get product statistics and some of products have multiple groups. Therefore, the amount of products is incorrect.

SELECT COUNT(p.id) AS total_product, SUM(p.price) AS total_price
FROM product p
LEFT JOIN attribute_group a ON
a.product_id = p.id
WHERE p.created_at >= "2018-01-01" AND (a.id = 1 OR a.id = 2)
GROUP BY p.id
LIMIT 0, 30;

product
id | price
1 | 100
2 | 150 
3 | 250

attribute_group
id | product_id | title
1 | 1 | a1
2 | 1 | a2
3 | 2 | a3
4 | 3 | a4

Should be:

1| 100

But i get:

2 | 200

Answer 1

You appear to want all products or the counts/sum of them that have attributes of both 1 and 2. Here is one method:

SELECT COUNT(*) as num_products, SUM(p.price) as total_price
FROM product p
WHERE p.created_at >= '2018-01-01' AND
      EXISTS (SELECT 1
              FROM attribute_group ag
              WHERE ag.product_id = p.id AND ag.id = 1
             ) AND
      EXISTS (SELECT 1
              FROM attribute_group ag
              WHERE ag.product_id = p.id AND ag.id = 2
             );