Total amount of columns in mySQL !

Question

hi ,

i have this sql :

SELECT * , (
xnum * xprice
) AS amount, SUM( xnum * xprice ) AS total
FROM xxbasket
LEFT JOIN xxsubproduct
USING ( xsubproduct_id )
LEFT JOIN xxcolor
USING ( xcolor_id )
WHERE xuserid = '3'

when i use SUM( xnum * xprice ) AS total it's gives me only one row , but when i remove this SUM( xnum * xprice ) it's give me all rows

My question is how can i get sum ? while i need all rows from query ?!

Answer 1

Hi

Sum is a function where the sum of your value (xnum * xprice) over all selected rows is calculated. The result is one row.

If you want to calculate the sum of multiple groups of rows, you may use the GROUP BY clause, which groups collums to groups (e.g. GROUP BY userid instead of your where clause would calculate SUM(xnum * xprice) for all users)

Answer 2

The SUM() function is an aggregate operator. The select it is associated with can no longer return individual rows, only information about groups of rows.

But you could return the sum in a subquery. Here's an example, assuming the xprice column is in the xxsubproduct table:

SELECT *, 
    xnum * xprice AS amount, 
    (   select sum(xnum * xprice)
        FROM xxbasket
        LEFT JOIN xxsubproduct USING xsubproduct_id
        WHERE xuserid = '3'
    ) as total
FROM xxbasket
LEFT JOIN xxsubproduct USING xsubproduct_id
LEFT JOIN xxcolor USING xcolor_id
WHERE xuserid = '3'