| Author |
 Topic  |
|
|
WRX
Starting Member
USA
3 Posts |
 Posted - 12/27/2012 : 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! |
Edited by - WRX on 12/28/2012 09:57:54
|
|
|
Bustaz Kool
Flowing Fount of Yak Knowledge
USA
1834 Posts |
Posted - 12/27/2012 : 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
Flowing Fount of Yak Knowledge
USA
7174 Posts |
Posted - 12/27/2012 : 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. |
Edited by - sodeep on 12/27/2012 17:12:07 |
|
|
|
WRX
Starting Member
USA
3 Posts |
Posted - 12/28/2012 : 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 a
inner join vndr_po_ln_vb b on a.vndr_po_id=b.vndr_po_id
inner join vndr_vb c on a.vndr_prty_id = c.vndr_prty_id
inner join itm_vb d on b.itm_id = d.itm_id
inner join sngl_sys_itm_vb e on d.sngl_sys_itm_id_seq = e.sngl_sys_itm_id_seq
where a.po_sts_cd in ('O','P','R')
and c.vndr_num in (116307,144307)
and Order_Dt = current_date
group by
1,2,3,4,5,6,7,8
) a
order by Order_Dt desc, Due_Dt, Appt_Dt, PO#, UPC |
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7174 Posts |
Posted - 12/28/2012 : 09:13:28
|
Try this
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
, 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 Cube
from vndr_po_vb a
inner join vndr_po_ln_vb b on a.vndr_po_id=b.vndr_po_id
inner join vndr_vb c on a.vndr_prty_id = c.vndr_prty_id
inner join itm_vb d on b.itm_id = d.itm_id
inner join sngl_sys_itm_vb e on d.sngl_sys_itm_id_seq = e.sngl_sys_itm_id_seq
Where a.po_sts_cd in ('O','P','R')
and c.vndr_num in (116307,144307)
and Order_Dt = current_date
group 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
USA
3 Posts |
Posted - 12/28/2012 : 09:55:10
|
| This works perfectly, thank you! |
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7174 Posts |
Posted - 12/28/2012 : 10:18:32
|
| You are welcome |
|
|
|
madhivanan
Premature Yak Congratulator
India
22864 Posts |
Posted - 01/02/2013 : 02:20:47
|
Note that using ordinal position of the columns in ORDER BY clause will work but not possible in GROUP BY clause
Madhivanan
Failing to plan is Planning to fail |
|
|
| |
Topic |
|
|
|
[SOLVED] Division affecting GROUP BY?