CODEWITHSUNDEEP
mysqlsqlsumconcatenation
user3685887
user3685887

Reputation: 1

How to use SUM() with multiple rows

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.

Upvotes: 0

Views: 63

Answers (1)

cha
cha

Reputation: 10411

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 ;

Upvotes: 1

Related Questions