sum of a column (which is itself a sum) in mysql?

Question

Basically, I have the following query:

SELECT 

sr.name AS salesrepname,
ct.name AS customertypename,
c.name AS customername,
c.code,
p.description AS productname,
p.unitofmeasure,
SUM(col.vatableprice) AS totalsales,
SUM(col.vatprice) AS vat, 
SUM(col.quantity) AS totalitems, 
SUM(col.quantity * col.costprice) AS totalsalecost,
SUM(col.vatableprice) - SUM(col.quantity * col.costprice) AS totalprofit ,


(SELECT SUM(col2.vatableprice) AS totalsales FROM customerorders AS co2 
LEFT JOIN customerorderlines AS col2 ON col2.customerorder_id = co2.id
LEFT JOIN customers AS c2 ON c2.id = co2.customer_id
LEFT JOIN locations AS l2 ON l2.id = c2.location_id 
LEFT JOIN customertypes AS ct2 ON ct2.id = c2.customertype_id 
LEFT JOIN salesreps AS sr2 ON sr2.id = c2.salesrep_id 
LEFT JOIN products AS p2 ON p2.id = col2.product_id 
LEFT JOIN suppliers AS s2 ON s2.id = p2.supplier_id 
WHERE c.id = c2.id AND co2.type = 2 
AND c2.salesrep_id = 20 AND p2.supplier_id = 67 
AND co2.orderdate >= '2010-01-01 00:00:00'
AND co2.orderdate <= '2010-08-31 23:59:59') AS credits ,

(SELECT SUM(col2.vatprice) AS totalvat FROM customerorders AS co2 
LEFT JOIN customerorderlines AS col2 ON col2.customerorder_id = co2.id 
LEFT JOIN customers AS c2 ON c2.id = co2.customer_id 
LEFT JOIN locations AS l2 ON l2.id = c2.location_id 
LEFT JOIN customertypes AS ct2 ON ct2.id = c2.customertype_id 
LEFT JOIN salesreps AS sr2 ON sr2.id = c2.salesrep_id 
LEFT JOIN products AS p2 ON p2.id = col2.product_id 
LEFT JOIN suppliers AS s2 ON s2.id = p2.supplier_id 
WHERE c.id = c2.id AND co2.type = 2 
AND c2.salesrep_id = 20 
AND p2.supplier_id = 67
AND co2.orderdate >= '2010-01-01 00:00:00' 
AND co2.orderdate <= '2010-08-31 23:59:59') AS creditsvat ,

(SELECT SUM(col2.quantity * col2.costprice) AS totalcreditcost 
FROM customerorders AS co2 
LEFT JOIN customerorderlines AS col2 ON col2.customerorder_id = co2.id 
LEFT JOIN customers AS c2 ON c2.id = co2.customer_id 
LEFT JOIN locations AS l2 ON l2.id = c2.location_id 
LEFT JOIN customertypes AS ct2 ON ct2.id = c2.customertype_id 
LEFT JOIN salesreps AS sr2 ON sr2.id = c2.salesrep_id 
LEFT JOIN products AS p2 ON p2.id = col2.product_id 
LEFT JOIN suppliers AS s2 ON s2.id = p2.supplier_id 
WHERE c.id = c2.id AND co2.type = 2 
AND c2.salesrep_id = 20 
AND p2.supplier_id = 67 
AND co2.orderdate >= '2010-01-01 00:00:00' 
AND co2.orderdate <= '2010-08-31 23:59:59') 
AS totalcreditcost

FROM customerorders AS co 
LEFT JOIN customerorderlines AS col ON col.customerorder_id = co.id
LEFT JOIN customers AS c ON c.id = co.customer_id 
LEFT JOIN locations AS l ON l.id = c.location_id 
LEFT JOIN customertypes AS ct ON ct.id = c.customertype_id 
LEFT JOIN salesreps AS sr ON sr.id = c.salesrep_id 
LEFT JOIN products AS p ON p.id = col.product_id
LEFT JOIN suppliers AS s ON s.id = p.supplier_id 
WHERE co.status_v = 5 
AND co.type = 1
AND c.salesrep_id = 20 AND p.supplier_id = 67
AND co.orderdate >= '2010-01-01 00:00:00'
AND co.orderdate <= '2010-08-31 23:59:59'
GROUP BY c.id 
ORDER BY customername

What I would like to achieve is a sum of the field aliased totalitems, which itself is a sum of col.quantity.

Please let me know if you want any more specifics.

Thanks in advance.

Answer 1

Take a look on WITH ROLLUP ;) http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html

Answer 2

You could save your first table into a temp table using the INTO keyword. Then perform a SUM(totalitems) just as you've done in the rest of your query.