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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 [SOLVED] Division affecting GROUP BY?

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!
Go to Top of Page

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.
Go to Top of Page

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 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
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-28 : 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
Go to Top of Page

WRX
Starting Member

3 Posts

Posted - 2012-12-28 : 09:55:10
This works perfectly, thank you!
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-28 : 10:18:32
You are welcome
Go to Top of Page

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 clause

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -