How do I use SUM in mySQL?

Question

I need to sum a group for my query. Something like:

SELECT customer_id, SUM(weekly) FROM earnings GROUP BY customer_id

The above works, but I need something a bit more complex, and my query is like this:

SELECT
    (SELECT SUM(weekly) FROM earnings) + 
    (SELECT SUM(day_earnings) FROM earnings) / .75

The above makes the sum of all the earnings, weekly and daily, but I need to group them by customer_id like in the first example. How do I achieve this?

Michael Berkowski · Accepted Answer · 2011-08-01 13:16:29Z

3

According to the order of operations in your attempt above, it's the SUM(day_earnings) only that's divided by .75. If it should be the weekly & day_earnings sums together, then put parentheses around them as (SUM(weekly) + SUM(day_earnings)) / 0.75 AS earnings.

SELECT 
  customer_id,
  customer_name,
  SUM(weekly) + SUM(day_earnings) / 0.75 AS earnings
FROM earnings JOIN customers ON earnings.customer_id = customers.customer_id
GROUP BY customer_id, customer_name

edited Aug 1, 2011 at 13:16

answered Aug 1, 2011 at 12:44

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

luqita Over a year ago

thanks! how can I also join that customer_id with the table customers to get the names? thank you!

Michael Berkowski Over a year ago

See change above. customer_name added to select list and group by list, and joined in the FROM

luqita Over a year ago

excellent! thanks! please notice however that customer_id needs the table before it since it exists in both. Thanks for your help!

Shafizadeh Over a year ago

why you did not use of table name in the sum() ? I thought if I'm using join and sum() then I should use some thing like this: sum(table.column), that is not correct ?

Michael Berkowski Over a year ago

@Sajad The table name is only necessary if more than one table in the FROM has a column of the same name. Otherwise the column name can be used alone, just as it could in the plain SELECT.

Derek · Accepted Answer · 2011-08-01 12:45:47Z

0

Does this work?

SELECT customer_id, SUM(weekly + (day_earnings / .75)) 
FROM earnings 
GROUP BY customer_id

answered Aug 1, 2011 at 12:45

Derek

23.5k8 gold badges55 silver badges61 bronze badges

Comments

StevieG · Accepted Answer · 2011-08-01 12:47:34Z

0

SELECT 
  customer_id, 
  SUM(weekly) weekly, 
  SUM(day_earnings) daily, 
  ((SUM(weekly) + SUM(day_earnings)) / .75) calc
FROM 
  earnings 
GROUP BY 
  customer_id

answered Aug 1, 2011 at 12:47

StevieG

8,7391 gold badge26 silver badges33 bronze badges

Comments

roberttdev · Accepted Answer · 2011-08-01 12:51:11Z

0

Can't you just do:

SELECT customer_id, (SUM(weekly) + SUM(day_earnings) / .75) AS earnSum FROM earnings GROUP BY customer_id

answered Aug 1, 2011 at 12:51

roberttdev

47.2k2 gold badges23 silver badges23 bronze badges

Collectives™ on Stack Overflow

How do I use SUM in mySQL?

4 Answers 4

5 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related