Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
WRX
Starting Member
3 Posts |
Posted - 2012-12-27 : 15:55:48
|
If this has already been asked, forgive me for I couldn't find any relevant search results.Currently I have a query that runs. The SELECT statement has 8 non-aggregate functions and a 9th: SUM(misc) as Ordered. I have GROUP BY 1,2,3,4,5,6,7,8 and it runs successfully.I add a 10th SELECT function: Ordered/num_units_on_truck. It gives me result 3504 "Selected non-aggregate values must be part of the associated group." When I change GROUP BY to 1,2,3,4,5,6,7,8,10 I get result 3625 "GROUP BY and WITH...BY clauses may not contain aggregate functions."What the heck? I add a new function, and it tells me to put it in the GROUP BY. I put it in the GROUP BY, and it says it can't be in the GROUP BY. Any idea what's going on here? Thank you! |
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2012-12-27 : 17:11:23
|
Can you show us the actual query? It isn't clear to me what you are trying to accomplish but, perhaps, with some specifics, a solution can be found.=================================================Hear the sledges with the bells - silver bells! What a world of merriment their melody foretells! |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-27 : 17:11:27
|
What is num_units_on_truck? Is it part of Non Aggregate function?You can do SUM(Misc)/NULLIF(num_units_on_truck,0) This will avoid divide by 0 error if there are any. Otherwise you need to apply aggregate functions on num_units_on_truck also. |
|
|
WRX
Starting Member
3 Posts |
Posted - 2012-12-28 : 08:46:56
|
What's in bold is what I'm having the issue with.select a.*from (select a.vndr_po_num as PO#, a.po_ordr_dt as Order_Dt, a.due_dt as Due_Dt, cast(a.apptmnt_dttm as date) as Appt_Dt, a.po_sts_cd as PO_Status, d.itm_num as CS_Code, trim(e.upc_vndr) || '-' || trim(e.upc_case) as UPC, e.sngl_sys_itm_desc as Description, sum(ordrd_qty) as Ordr_qty_cases,1.0*Ordr_qty_cases/(d.WHS_NUM_OF_CASES_ON_A_TIER*d.WHS_NUM_OF_TIERS_ON_A_PLT) as Ordr_qty_plts,1.0*Ordr_qty_cases*d.case_cube as "Cube"from vndr_po_vb ainner join vndr_po_ln_vb b on a.vndr_po_id=b.vndr_po_idinner join vndr_vb c on a.vndr_prty_id = c.vndr_prty_idinner join itm_vb d on b.itm_id = d.itm_idinner join sngl_sys_itm_vb e on d.sngl_sys_itm_id_seq = e.sngl_sys_itm_id_seqwhere a.po_sts_cd in ('O','P','R')and c.vndr_num in (116307,144307)and Order_Dt = current_dategroup by1,2,3,4,5,6,7,8) aorder by Order_Dt desc, Due_Dt, Appt_Dt, PO#, UPC |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-28 : 09:13:28
|
Try thisSelect a.vndr_po_num as PO#, a.po_ordr_dt as Order_Dt, a.due_dt as Due_Dt, cast(a.apptmnt_dttm as date) as Appt_Dt, a.po_sts_cd as PO_Status, d.itm_num as CS_Code, trim(e.upc_vndr) || '-' || trim(e.upc_case) as UPC, e.sngl_sys_itm_desc as Description, SUM(ordrd_qty) as Ordr_qty_cases, SUM(ordrd_qty) * 1.0/NULLIF(SUM((d.WHS_NUM_OF_CASES_ON_A_TIER*d.WHS_NUM_OF_TIERS_ON_A_PLT)),0) as Ordr_qty_plts, SUM(ordrd_qty) * SUM(d.case_cube) as Cubefrom vndr_po_vb ainner join vndr_po_ln_vb b on a.vndr_po_id=b.vndr_po_idinner join vndr_vb c on a.vndr_prty_id = c.vndr_prty_idinner join itm_vb d on b.itm_id = d.itm_idinner join sngl_sys_itm_vb e on d.sngl_sys_itm_id_seq = e.sngl_sys_itm_id_seqWhere a.po_sts_cd in ('O','P','R')and c.vndr_num in (116307,144307)and Order_Dt = current_dategroup by a.vndr_po_num, a.po_ordr_dt, a.due_dt as Due_Dt, cast(a.apptmnt_dttm as date), a.po_sts_cd, d.itm_num, trim(e.upc_vndr) || '-' || trim(e.upc_case), e.sngl_sys_itm_desc |
|
|
WRX
Starting Member
3 Posts |
Posted - 2012-12-28 : 09:55:10
|
This works perfectly, thank you! |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-28 : 10:18:32
|
You are welcome |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2013-01-02 : 02:20:47
|
Note that using ordinal position of the columns in ORDER BY clause will work but not possible in GROUP BY clauseMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|