Sum a subquery

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

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.

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

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

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:-

Code:

---

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

---

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

Code:

---

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)

---

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:-

Code:

---

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)

---

stil doesn't work . how sad :(

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

syntax error near " as mybasevalues" :(

Sorry, my fault:blush:.

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

bound error :(

I have no idea what that meant...:rolleyes: