CODEWITHSUNDEEP
mysqlaggregate-functions
SMP
SMP

Reputation: 35

multiply two columns from two different tables and group them together

I have two tables in MySQL which are sale and material details. I want to calculate the profit I made by selling items which is profit = (total) -(qty* landedcost)
Here is the structure of the two tables: sale and materialdetails table

This is the query

SELECT sale.name ,sale.total-(sale.qty * materialdetails.landingcost) AS 
result
FROM sale JOIN materialdetails
on sale.id = materialdetails.id
GROUP BY sale.name,result;

the result i get : query result

I want something like this 

  name          result
  A4             5000
 Computer        40000
 Flash memory    1000
 Headphone       22000
 Mobile         35000

Any idea please?

Upvotes: 1

Views: 3247

Answers (1)

user2314737
user2314737

Reputation: 29317

You should sum the result and group by sale.name only, something like this:

SELECT sale.name ,sum(sale.total-(sale.qty * materialdetails.landingcost)) AS 
result
FROM sale JOIN materialdetails
on sale.id = materialdetails.id
GROUP BY sale.name;

Explanation: if you group by two fields GROUP BY sale.name,result you will get one line for all records that have the same sale.name and result, so for instance 

name     result

Computer 10000
Computer 25000

are two different lines and they are not grouped together as one.

Upvotes: 2

Related Questions