I have a problem guys, i need to sum all the values that is retrived in column coil and kilograms, my problem is i dont know how, cause i use subquery and aggregate fuinctions: look at my example
Code:
SELECT I.ITEMNAME [RAW MATERIAL] , I.MATERIALSIZE, I2.ITEMNAME PRODUCTS, 
CASE 
WHEN it.UNITMEASUREID = 11 THEN
IT.QUANTITY
when it.UNITMEASUREID = 1
then   It.quantity / (select unitvalue from unitmeasureconversions where unitmeasureid = 1 And unitmeasureconversions.itemid = it.itemid)*
(select unitvalue from unitmeasureconversions where unitmeasureid = 11 And unitmeasureconversions.itemid = it.itemid )
else 0 end Coil,
Case
WHEN it.UNITMEASUREID = 1 THEN
IT.QUANTITY  Else  It.quantity / (select unitvalue from unitmeasureconversions where unitmeasureid = 1 And unitmeasureconversions.itemid = it.itemid)*
(select unitvalue from unitmeasureconversions where unitmeasureid = 11 And unitmeasureconversions.itemid = it.itemid) end Kilogram

FROM ITEMTRANSACTIONS IT
JOIN DEPARTMENTS D ON D.DEPARTMENTID = IT.DEPARTMENTID
JOIN SECTIONS S ON S.SECTIONID = IT.SECTIONID
JOIN SUBSECTIONS SUBS ON SUBS.SUBSECTIONID = IT.SUBSECTIONID
JOIN SUBSECTIONAREAS SUBA ON SUBA.SUBSECTIONAREAID = IT.SUBSECTIONAREAID
JOIN ITEMS I ON  I.ITEMID = IT.ITEMID
JOIN ITEMS I2 ON I2.ITEMID = IT.ASSIGNTOITEMID
JOIN UNITMEASURES UM ON UM.UNITMEASUREID = IT.UNITMEASUREID
JOIN UNITMEASURECONVERSIONS UMC ON UMC.UNITMEASUREID = IT.UNITMEASUREID And UMC.itemid  = it.ITEMID
Join Machines M on m.MachineId = it.machineid
WHERE D.DEPARTMENTID = 4 AND S.SECTIONID = 13 AND SUBS.SUBSECTIONID =  4 AND SUBA.SUBSECTIONAREAID = 2
AND (UM.UnitmeasureID = 11 Or UM.UnitmeasureID = 1)
all i want is to to do something like this but i have an error:
Code:
SELECT I.ITEMNAME [RAW MATERIAL] , I.MATERIALSIZE, I2.ITEMNAME PRODUCTS, 
CASE 
WHEN it.UNITMEASUREID = 11 THEN
Sum(IT.QUANTITY)
when it.UNITMEASUREID = 1
then   Sum(It.quantity / (select unitvalue from unitmeasureconversions where unitmeasureid = 1 And unitmeasureconversions.itemid = it.itemid)*
(select unitvalue from unitmeasureconversions where unitmeasureid = 11 And unitmeasureconversions.itemid = it.itemid ))
else 0 end Coil,
Case
WHEN it.UNITMEASUREID = 1 THEN
Sum(IT.QUANTITY)  Else  Sum(It.quantity / (select unitvalue from unitmeasureconversions where unitmeasureid = 1 And unitmeasureconversions.itemid = it.itemid)*
(select unitvalue from unitmeasureconversions where unitmeasureid = 11 And unitmeasureconversions.itemid = it.itemid)) end Kilogram

FROM ITEMTRANSACTIONS IT
JOIN DEPARTMENTS D ON D.DEPARTMENTID = IT.DEPARTMENTID
JOIN SECTIONS S ON S.SECTIONID = IT.SECTIONID
JOIN SUBSECTIONS SUBS ON SUBS.SUBSECTIONID = IT.SUBSECTIONID
JOIN SUBSECTIONAREAS SUBA ON SUBA.SUBSECTIONAREAID = IT.SUBSECTIONAREAID
JOIN ITEMS I ON  I.ITEMID = IT.ITEMID
JOIN ITEMS I2 ON I2.ITEMID = IT.ASSIGNTOITEMID
JOIN UNITMEASURES UM ON UM.UNITMEASUREID = IT.UNITMEASUREID
JOIN UNITMEASURECONVERSIONS UMC ON UMC.UNITMEASUREID = IT.UNITMEASUREID And UMC.itemid  = it.ITEMID
Join Machines M on m.MachineId = it.machineid
WHERE D.DEPARTMENTID = 4 AND S.SECTIONID = 13 AND SUBS.SUBSECTIONID =  4 AND SUBA.SUBSECTIONAREAID = 2
AND (UM.UnitmeasureID = 11 Or UM.UnitmeasureID = 1)
group by m.machineID
i get an error sayin: cannot use sum function with an aggregate function or subquery inside of it.. sounds like that. Any help or option how to fix this thing? Thanks