How to use SUM() with multiple rows

Question

I have this code already written which displays a table and how much a customer has spent. This data is taken from multiple tables and is joined as such.

SELECT c.lastname, c.firstname,
    CONCAT(oi.quantity*oi.paideach) as 'Total Spent'
FROM customers c INNER JOIN orders o USING(customer_num)
    INNER JOIN orderitems oi USING(order_num)
ORDER BY c.lastname, c.firstname ;

My question is, how do I sum the orders together to get an order total, as of now I am only getting

_______________________________________________________
| Lastname         Firstname              Ordertotal  |
| Jim              Bob                    170.93      |
| Jim              Bob                    15.02       |
| Rob              Milford                11.30       |
| Rob              Milford                20.59       |
| Rob              Milford                59.49       |
|_____________________________________________________|

Im really frustrated and just need some sort of solution to this. Ive tried adding SUM() before my concat statement but it leaves me summing all the values instead of summing up everyones totals as its supposed to.

cha · Accepted Answer · 2014-05-29 02:10:12Z

1

You need to use SUM:

SELECT c.lastname, c.firstname,
    SUM(oi.quantity*oi.paideach) as 'Total Spent'
FROM customers c INNER JOIN orders o USING(customer_num)
    INNER JOIN orderitems oi USING(order_num)
GROUP BY c.lastname, c.firstname
ORDER BY c.lastname, c.firstname ;

answered May 29, 2014 at 2:10

cha

10.4k1 gold badge20 silver badges27 bronze badges

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

1 Comment

user3685887 Over a year ago

Thank you very much :) I should have realized that mistake before haha.

Collectives™ on Stack Overflow

How to use SUM() with multiple rows

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related