Multiply Columns?

[nikko50]

How can I multiply columns to get a "grand total"? I would like to multiply the 'total' alias with unitcost to get the grand total.
SELECT nsidetails.equipmentid, nsidetails.projectnum,nsidetails.unitcost,
COUNT(nsidetails.equipmentid) AS 'total',
equipment.description
FROM nsidetails
LEFT JOIN equipment ON equipment.itemid=nsidetails.equipmentid
WHERE nsidetails.ncostoff = 'ESSED'
GROUP BY nsidetails.equipmentid

[paulnaj]

I think you can just use the SUM() function instead of COUNT() ...

SELECT 	nsidetails.equipmentid
, 	nsidetails.projectnum
, 	equipment.description
, 	SUM(nsidetails.unitcost) AS `total`
FROM nsidetails
LEFT JOIN equipment ON equipment.itemid = nsidetails.equipmentid
WHERE nsidetails.ncostoff = 'ESSED'
GROUP BY nsidetails.equipmentid

... since the 'GROUP BY' clause already does the organising of the records.

Paul 🙂

[nikko50]

Hi Paul, I need to multiply the quantity * unitcost in the query . Can I do this??

[paulnaj]

Hi nikko,

This is what's happening anyway ...

For each 'nsidetails.equipmentid', the unitcost will be the same, so, for example, if there were 20 entries for a particular equipmentid, then the SUM would be unitcost + unitcost + ... + unitcost = 20*unitcost.

In general, you cannot operate on AGGREGATE functions, so (eg) COUNT(id)*unitcost is not going to work.

Paul.