I try to select the favorites for each wines in my database and all data from wines but with the group by obligation, I can’t have data for wines which don’t have a favorite_score, so do you know how to do please.
SELECT SUM(wine_id) as favorite_score, wines.* FROM wines JOIN favorites ON favorites.wine_id = wines.id WHERE wine_color in ('White') AND available = 1 GROUP BY wines.id ORDER BY wines.id
This is my SQL request for instance and now this my DB diagram : enter image description here
So, I want all data for each wine and favorite_score too for each one or 0 if there is no data corresponds on wines.id = favorites.wine_id Sorry for my English, I’m French
Advertisement
Answer
I wouldn’t do a sum, because that will sum all the wine_id
s and not the number of wine_id
s in the table favorites.
for example, you have wine_id = {50, 100,123}
and a SUM(wine_id)
would be 273, but I assume that what you want is 3 instead. So based on that, the query I would do is as follows
select count(f.wine_id) as favorite_score, w.* from wines as w join favorites as f on w.id = f.wine_id WHERE w.wine_color in ('White') AND w.available = 1 group by w.id, w.price, w.certification_label, w.label_name, w.vintage, w.oenological_comment, w.area, w.city, w.variety_of_wine, w.age_of_vineyard, w.soil, w.prunning, w.harvest, w.vinification, w.quantity, w.barrel_fermentation, w.image_path, w.available, w.wine_color, w.award, w.award_path, w.extra_comment order by w.id
Let me know if you have any questions. Also, don’t forget that for the count
to work you need to add all the other fields in the group by
, so I would advice you to only take the necessary fields.