Sum/Group By Issue

[gkrenton]

Created this statement

select
IP.inv_id,
IP.proxy_inv_id||SZ.si_code as Option_ID0,
IP.inv_id||(GS.col_num+276) as Option_ID1,
IP.inv_id||(GC.col_num+136) as Option_ID2
from ecat_item_proxy as IP Inner Join sizes as SZ ON
IP.proxy_inv_size_id=SZ.si_size
Inner join ecat_items as EI on IP.inv_id=EI.inv_id
Inner join ecat_web_size2 as EW2 on IP.proxy_inv_size2_id=EW2.inv_size2_id
Inner Join gina_size2 as GS on GS.size2_name=EW2.inv_size2_id
Inner join gina_color as GC on IP.proxy_inv_color_id=GC.col_name
Inner join item_stores as IS on IP.proxy_inv_id=IS.item_id AND IP.proxy_inv_size2_id=item_size2_id AND
IP.proxy_inv_size_id=IS.item_size_id AND
IP.proxy_inv_color_id=IS.item_color_id

Which works amazingly enough fine & dandy - however when I add the statement
sum(IS.on_hand_qty) as below & add the group by clause it errors out & I'm stuck as to what I'm missing. Can't find documentation indicating if I can call the colums as their aliases but using the 'formulas' also error out so I'm stumped.

select
IP.inv_id,
IP.proxy_inv_id||SZ.si_code as Option_ID0,
IP.inv_id||(GS.col_num+276) as Option_ID1,
IP.inv_id||(GC.col_num+136) as Option_ID2,
Sum(IP.on_hand_qty)
from ecat_item_proxy as IP Inner Join sizes as SZ ON
IP.proxy_inv_size_id=SZ.si_size
Inner join ecat_items as EI on IP.inv_id=EI.inv_id
Inner join ecat_web_size2 as EW2 on IP.proxy_inv_size2_id=EW2.inv_size2_id
Inner Join gina_size2 as GS on GS.size2_name=EW2.inv_size2_id
Inner join gina_color as GC on IP.proxy_inv_color_id=GC.col_name
Inner join item_stores as IS on IP.proxy_inv_id=IS.item_id AND IP.proxy_inv_size2_id=item_size2_id AND
IP.proxy_inv_size_id=IS.item_size_id AND
IP.proxy_inv_color_id=IS.item_color_id
group by IP.inv_id, Option_ID0, Option_ID1, Option_ID2

Help?

Thanks
Gina

 

[PHV]

Have you tried this ?
group by 1, 2, 3, 4

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[gkrenton]

Nope hadn't tried that & wish it worked but it still errors out.....

-Gina

 

[PHV]

Any chance you could post the error message ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[gkrenton]

Found the 'issue' finally - fresh morning eyes do it every time.
Missing the alliance in this part of the statement
AND IP.proxy_inv_size2_id=item_size2_id & then had to rework the order of the joins (seems it didn't like the actual order)
And then Group by 1,2,3,4 Works fine!
Thanks a ton - I certainly appreciate all your help as I work through this little project.

- Gina