Sum a subquery

[hoobas20]

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

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:

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

[jmcilhinney]

You would need to use a temp table to store intermediate data. You can then perform a subsequent query against that data just like any other table.

[hoobas20]

Is it efficient? I'll be using it against the crystal reports 10. Is there any ways to do this thing there. thanks men, you always answers my question

[jmcilhinney]

Using temp tables does slow a query down but you gotta do what you gotta do.

[FunkyDexter]

I had a very similar problem recently and, as usual, VBF provided the answer. You select from a subquery in which you've obtained all the values you need to:-

Select ItemName
         ...
         case when UnitMeasured = 11
            then quantity
         when UnitMeasured = 1
            then quanity/Kilogram
         end
From
(Select ItemName, 
          ...
          UnitMeasured, 
          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) as Kilogram
From ItemTransactions
Inner Join ...
) as MyBaseValues

[hoobas20]

please help me o fix this. i got a syntax error near last line.. thanks

SELECT 
M.MACHINENAME, 
I.ITEMNAME [RAW MATERIAL], 
I.MATERIALSIZE, 
I2.ITEMNAME PRODUCTS, 
CASE 
	WHEN IT.UNITMEASUREID = 11 THEN
	sum(IT.QUANTITY) Else sum(IT.QUANTITY/ coil) End Coil2,
	
CASE
	WHEN IT.UNITMEASUREID = 1 THEN
	sum(IT.QUANTITY) else sum(IT.QUANTITY / coil) end Kilgram2
From  (

	Select (SELECT UNITVALUE FROM UNITMEASURECONVERSIONS WHERE UNITMEASUREID = 1 AND UNITMEASURECONVERSIONS.ITEMID = IT.ITEMID)*
	(SELECT UNITVALUE FROM UNITMEASURECONVERSIONS WHERE UNITMEASUREID = 11 AND UNITMEASURECONVERSIONS.ITEMID = IT.ITEMID )
	As COIL 
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.ITEMNAME,I.MATERIALSIZE, I2.ITEMNAME)

[FunkyDexter]

I made exactly the same mistake when I did this. You have to give the subselect an alias. I have no idea why. Add an alias just after your group by:-

SELECT 
M.MACHINENAME, 
I.ITEMNAME [RAW MATERIAL], 
I.MATERIALSIZE, 
I2.ITEMNAME PRODUCTS, 
CASE 
	WHEN IT.UNITMEASUREID = 11 THEN
	sum(IT.QUANTITY) Else sum(IT.QUANTITY/ coil) End Coil2,
	
CASE
	WHEN IT.UNITMEASUREID = 1 THEN
	sum(IT.QUANTITY) else sum(IT.QUANTITY / coil) end Kilgram2
From  (

	Select (SELECT UNITVALUE FROM UNITMEASURECONVERSIONS WHERE UNITMEASUREID = 1 AND UNITMEASURECONVERSIONS.ITEMID = IT.ITEMID)*
	(SELECT UNITVALUE FROM UNITMEASURECONVERSIONS WHERE UNITMEASUREID = 11 AND UNITMEASURECONVERSIONS.ITEMID = IT.ITEMID )
	As COIL 
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.ITEMNAME,I.MATERIALSIZE, I2.ITEMNAME
as MyBaseValues)

[hoobas20]

stil doesn't work . how sad

[FunkyDexter]

What error message are you getting and what line is highlighted when you click on it?

[hoobas20]

syntax error near " as mybasevalues"

[FunkyDexter]

Sorry, my fault.

The final bracket's in the wrong place. Move it so it's between I2.ItemName and AS. That should sort it for you.

[hoobas20]

bound error

[FunkyDexter]

I have no idea what that meant...