Search code examples
mysqljoingroup-bysum

mysql groupby and join

I want to fetch total from bar_purchase_request table group by menu type like "food" and "drink". I have tried with below query :

SELECT  DISTINCT(b.name),
        0 as food_total,
        0 as drink_total,
        sum(ph.total) as total,
        sum(ph.gratuity) as gratuity
    from  branches b
    LEFT JOIN  drink_requests dr  ON b.id = dr.branch_id
    JOIN  drink_food df  ON df.id = dr.drink_id
    JOIN  bar_purchase_history ph  ON ph.request_id = dr.id
    GROUP BY  dr.branch_id;

you can check my table structure as below : enter image description here

request table structure : enter image description here

menu item table structure : enter image description here

I want my result like : enter image description here

Anyone can help please.

Solution

  • 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;