user3383017 user3383017 - 4 months ago 13
SQL Question

Group By and multiply Each Row With Two different column

Quantity Litre BillNo
2 1.00 1
3 0.76 1
5 1.00 2
5 1.00 2


Expected Result

Quantity Litre BillNo
5 4.28 1
10 10.00 2


How to count litre
in BillNo 1

2 Quantity * 1.00 Litre = 2.00 Litre

3 Quantity * 0.76 Litre = 2.28 Litre

So Total litre Of Bill 1 is 4.28 and Quantity 2+3=5,

Same as in Bill 2, How can i get Expected Result.

Sorry if i can't explain well......

Answer

Use sum and group by

 select 
   sum(quantity) as  quantity
 , sum(litre) as litre
 , sum(quanty*litre) as Total
 , billNo 
 from my_table 
 group by biiNo