CODEWITHSUNDEEP
mysqlsqlsum
Rudy van Sloten
Rudy van Sloten

Reputation: 195

MySQL SUM() on identical cells in different columns

I have a database on which I run the following query:

SELECT products.name AS 'Product', 
products_used.amount * procedures_perf.amount AS 'Total' 
    FROM products_used 
    INNER JOIN procedures ON procedures.pr_id = products_used.pr_id 
    INNER JOIN products ON products.p_id = products_used.p_id 
    INNER JOIN procedures_perf ON procedures_perf.pr_id = products_used.pr_id 
    INNER JOIN hospitals ON hospitals.h_id = procedures_perf.h_id 
    WHERE hospitals.h_id = "20001"

This produces the following excerpt:

Product     Total
-----------------   
retractor   402
EB          402
EB          0
retractor   105
EB          0

So there are a number of procedures, which all use a specific set of products. I am trying to select the amount of total products used, whereas it is giving me a list of each product used per procedure. The outcome I would want is this:

Product     Total 
-----------------
retractor   507
EB          402

I have tried to use SUM() on the multiplication, but it keeps grabbing the total of all products. The only solution I could find online is one where the "Product" field would have to be listed in an IF-clause, but I do not want to hardcore the product names in an IF-statement, as it is an ever expanding list. I am probably overlooking something really obvious...

JUST TO BE CLEAR: Group by products.name is not adding up the total values, it just takes the first value it gets. SUM(x * y) suggestion is throwing out an obscenely large number.

Upvotes: 0

Views: 84

Answers (2)

FallAndLearn
FallAndLearn

Reputation: 4135

Use GROUP BY 

SELECT product, SUM(total) as total
FROM tablename
GROUP BY product;

After changing your whole query

SELECT products.name AS 'Product', 
SUM(products_used.amount * procedures_perf.amount) AS 'Total' 
FROM products_used
INNER JOIN procedures ON procedures.pr_id = products_used.pr_id 
INNER JOIN products ON products.p_id = products_used.p_id 
INNER JOIN procedures_perf ON procedures_perf.pr_id = products_used.pr_id 
INNER JOIN hospitals ON hospitals.h_id = procedures_perf.h_id 
WHERE hospitals.h_id = "20001" 
GROUP BY products.name;

Upvotes: 0

Zafar Malik
Zafar Malik

Reputation: 6854

You need to use of group by-

SELECT products.name AS 'Product', 
SUM(products_used.amount * procedures_perf.amount) AS 'Total' 
    FROM products_used 
    INNER JOIN procedures ON procedures.pr_id = products_used.pr_id 
    INNER JOIN products ON products.p_id = products_used.p_id 
    INNER JOIN procedures_perf ON procedures_perf.pr_id = products_used.pr_id 
    INNER JOIN hospitals ON hospitals.h_id = procedures_perf.h_id 
    WHERE hospitals.h_id = "20001" 
GROUP BY products.name;

Upvotes: 2

Related Questions