How to count favorites and all data of an other table with JOIN

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

Answer

I wouldn’t do a sum, because that will sum all the wine_ids and not the number of wine_ids 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.