mysql groupby and join-CodePudding

The

request table structure :

menu item table structure :

I want my result like :

Anyone can help please.

CodePudding user response：

I created the query and its working for me :

SELECT b.name as branch_name,SUM(CASE WHEN df.type = 'drink' THEN bph.price ELSE 0 END) as totalDrink,SUM(CASE WHEN df.type = 'food' THEN bph.total ELSE 0 END) as totalFood,SUM(bph.gratuity) as gratuity, sum(bph.total) as total FROM bar_purchase_history bph JOIN drink_food df ON bph.item_id = df.id JOIN drink_requests dr ON bph.request_id = dr.id JOIN branches b ON dr.branch_id = b.id where dr.branch_id = 1 GROUP BY dr.branch_id;

CodePudding user response：

(Not yet an "Answer")

DISTINCT is sort of a subset of GROUP BY. I can't think of a case where it is useful to have both in the same SELECT.

Furthermore, DISTINCT is not a 'function', as you have written it; DISTINCT applies to the entire row.

Remove DISTINCT.

You may need a subquery to achieve your goal. But I am unclear on the goal.

I want to fetch total from bar_purchase_request table group by menu type like "food" and "drink".

I need a bit more description of what you are trying to achieve. What are "branches"? Is name a person's name or, since it is in the table drink_food, a food name like 'pretzels' and 'martini'?