Mohamed Melouk Mohamed Melouk - 7 months ago 12
SQL Question

mysql group by and sum results from 2 fields from two tables

I have

bills
,
bill_details
,
customers
and
suppliers
tables
I need to make a query to return the total sales and total returns from
area
filed in
customers
and
suppliers
table

I need the sum of the same areas of all customers and suppliers

select c.area,s.area,
sum( IF(b.bill_type = 'sales', bd.quantity*(bd.price-bd.discount), 0) ) as totalSales,
sum( IF(b.bill_type = 'salesReturns', bd.quantity*(bd.price-bd.discount), 0) ) as totalReturns,
sum( IF(b.bill_type = 'sales', bd.quantity, 0) ) as sales,
sum( IF(b.bill_type = 'salesReturns', bd.quantity, 0) ) as returns
from adsl_bill b
left join bill_details bd on bd.bill_num = b.bill_num && bd.bill_type = b.bill_type
left join customers c on b.customer = c.id && b.customer_type = 1
left join suppliers s on b.customer = s.id && b.customer_type = 2

group by area


I know
group by area
is wrong statement but I just need to group and sum with both s.area and c.area

Answer

Use

select ifnull(c.area, s.area) as area,
        sum( IF(b.bill_type = 'sales', bd.quantity*(bd.price-bd.discount), 0) ) as totalSales,
        sum( IF(b.bill_type = 'salesReturns', bd.quantity*(bd.price-bd.discount), 0) ) as totalReturns,
        sum( IF(b.bill_type = 'sales', bd.quantity, 0) ) as sales,
        sum( IF(b.bill_type = 'salesReturns', bd.quantity, 0) ) as returns
    from adsl_bill b
    left join bill_details bd on bd.bill_num = b.bill_num && bd.bill_type = b.bill_type
    left join customers c on b.customer  = c.id && b.customer_type = 1
    left join suppliers s on b.customer  = s.id && b.customer_type = 2
group by area