CODEWITHSUNDEEP
mysql
sandeepKumar
sandeepKumar

Reputation: 811

Want to get all data from three tables in single query with group by function

What i Need: I want to show the sales by dish category

I have three tables for this problem:

1.order(dish_id,dish_price,dish_quantity)
2.dish(dish_id,dish_name,dish_cat_id)
3.dish_category(dish_cat_id,dis_cat_name

what result i want is : I want to show the report that , how many quantity has been sold, by dish category wise , and the total price , total_quantity

result to be expected: dish_cat_id, dish_cat_name, total_price , total_quantity_sold

what i have tried:

    SELECT  d.dish_name, 
            d.dish_id,
            SUM(o.price) as total_price,
            SUM(o.dish_quantity) as total_qauntity 
    FROM order as o , dish as d
    WHERE o.dish_id = d.dish_id
    GROUP BY o.dish_id

Now here i can get all the dish and their total price by dish wise , how to i get them dish_category wise

As right i am getting no idea expected to fire another query on the selected result about this query to get them dish_category wise

Upvotes: 0

Views: 129

Answers (3)

Rohit Kumar
Rohit Kumar

Reputation: 119

SELECT dc.dis_cat_name, 
       SUM(o.price) as total_price, 
       SUM(o.dish_quantity) as total_quantity 
FROM order o 
    INNER JOIN dish d ON o.dish_id = d.dish_id   
    INNER JOIN  dish_category dc ON dc.dish_cat_id = d.dish_cat_id
 GROUP BY dc.dish_cat_id;

Upvotes: 2

Amit Garg
Amit Garg

Reputation: 3907

SELECT  c.dis_cat_name , c.dish_cat_id ,
        SUM(o.price) as total_price ,
        SUM(o.dish_quantity) as total_qauntity 

FROM order as o JOIN  dish as d

       ON (o.dish_id = d.dish_id)
       join dish_category c on (d.dish_cat_id=c.dish_cat_id)

GROUP BY d.dish_cat_id

Upvotes: 2

Grisha Weintraub
Grisha Weintraub

Reputation: 7986

You have to join all 3 tables :

select d2.dish_cat_id, d2.dish_cat_name, 
       sum(o1.dish_price) total_price ,sum(o1.dish_quantity) total_quantity_sold
from dish d1 join dish_category d2 on (d1.dish_cat_id= d2.dish_cat_id)
             join order o1 on (d1.dish_id = o1.dish_id)
group by d2.dish_cat_id, d2.dish_cat_name

Upvotes: 1

Related Questions