CODEWITHSUNDEEP
mysql
Bullfinch
Bullfinch

Reputation: 255

Mysql SUM of SUM

Is it possible to make a sum of sum in one select query?

Something like this:

SELECT id, SUM(current_price - bought_price)*amount AS profit FROM purchase WHERE purchase_id = 1 GROUP BY id

I want the sum of all returned profit

Upvotes: 0

Views: 2590

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

This is your query:

SELECT id, SUM(current_price - bought_price)*amount AS profit
FROM purchase
WHERE purchase_id = 1
GROUP BY id ;

If you want the total profit, just remove the group by and rephrase the sum():

SELECT SUM(current_price*amount - bought_price*amount) AS profit
FROM purchase
WHERE purchase_id = 1;

EDIT:

You can also write this as:

SELECT SUM((current_price - bought_price)*amount) AS profit
FROM purchase
WHERE purchase_id = 1;

By the way, the original formulation isn't correct. You want to use the same form with the group by:

SELECT id, SUM((current_price - bought_price)*amount) AS profit
FROM purchase
WHERE purchase_id = 1
GROUP BY id;

The problem with your version is that amount is taken from on arbitrary row for each id. If there is only one row in purchase for each id, then no problem. But if there are multiple rows for an id, then the query in the question will produce indeterminate results. (You are using a MySQL extension to group by that is documented here.)

Upvotes: 0

Sergei N Lukin
Sergei N Lukin

Reputation: 575

SELECT id, SUM(current_price - bought_price)*amount 
AS profit FROM purchase WHERE purchase_id = 1 GROUP BY id WITH ROLLUP;

You can use WITH ROLLUP modifier for GROUP BY (http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html). As result you will get all summary by product and TOTAL for all product groups.

Upvotes: 0

Bobby
Bobby

Reputation: 302

This is what you are asking:

SELECT tmp.id, SUM(tmp.profit)
FROM (
  SELECT id, SUM(current_price - bought_price)*amount AS profit 
  FROM purchase 
  WHERE purchase_id = 1
  GROUP BY id 
) AS tmp

but the result is the same as

SELECT SUM(current_price - bought_price)*amount AS profit 
FROM purchase 
WHERE purchase_id = 1

Upvotes: 1

Related Questions