trying to do a SUM() containing a multiplication

steamPunk

I need to get the total cost of all the products in an order by doing

SUM(AR_PrixHT * QUANTITE)

where AR_PrixHT is the product price column and QUANTITE is the product quantity column

here's my entire query

SELECT ID_Commande_Site, POINT_LIVRAISON, PL_INTITULE, SUM(AR_PrixHT * QUANTITE) AS sum_lines FROM comm_en AS t1 LEFT JOIN pl AS t2 ON t2.PL_ID=t1.POINT_LIVRAISON LEFT JOIN comm_li AS t3 WHERE t3.ID_Commande_Site=t1.ID_Commande_Site WHERE CT_Client='BILLJONES' AND DATE_LIVRAISON='2008-07-22' GROUP BY sum_lines
//
//

I'm getting an error that "supplied argument is not a valid MySQL result resource" - does this mean that you can't multiply inside a SUM() ?

thanks

steamPunk

it seems i can't group by sum lines (it gives a mysql_error) but if i do

SELECT t1.ID_Commande_Site, POINT_LIVRAISON, PL_INTITULE, SUM(AR_PrixHT * QUANTITE) AS sum_lines FROM comm_en AS t1 LEFT JOIN pl AS t2 ON t2.PL_ID=t1.POINT_LIVRAISON LEFT JOIN comm_li AS t3 ON t3.ID_Commande_Site=t1.ID_Commande_Site WHERE CT_Client='BILLJONES' AND DATE_LIVRAISON='2008-07-22' GROUP BY AR_PrixHT
//
//

then it mulitplies the two cols in each row but doesn't do the sum for all rows

anyone got any ideas ?

leatherback

You need to set the group by field to the field that identify the groups, e.g., a product ID, user ID or -as you have done- the price. Now.. I am not sure what you are after. If you only have one entry per AR_PrixHT is cannot sum whe you group by it, or effectively it DOES sum, but if you sum one item the value is the sam as for that one item.

I think you meant to group by user id or something.

steamPunk

it's kind of complicated - but each order has an entry in the 'comm_en' table and the items in the order are stored in 'comm_li'

what i'd like to do is, for each item associated with the order number, to select each price and multiply it by the quantity of the item and then return the sum as the total cost of the order

at the moment, the query is giving me 1 row for each item - it would be cool if i could return 1 row for each order with all the multiplying being done by mysql

it would be like "SELECT .... SUM (of the cost of all items multiplied by the quantity of each item)

does that make more sense ?

leatherback

so you are looking for group by ORDERID

steamPunk

um yes you're probably right - i'll have to have a think about that one !

thanks

leatherback

Why think?
I presume you use the order number for linking products to the order? So you use that field as the grouping field and you should be on your way, laughing and screaming 😃