Mohamed Melouk - 1 year ago 33

SQL Question

I have

`bills`

`bill_details`

`customers`

`suppliers`

I need to make a query to return the total sales and total returns from

`area`

`customers`

`suppliers`

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`

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
```