SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 [SOLVED] Division affecting GROUP BY?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

WRX
Starting Member

USA
3 Posts

Posted - 12/27/2012 :  15:55:48  Show Profile  Reply with Quote
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
1641 Posts

Posted - 12/27/2012 :  17:11:23  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/27/2012 :  17:11:27  Show Profile  Reply with Quote
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
Go to Top of Page

WRX
Starting Member

USA
3 Posts

Posted - 12/28/2012 :  08:46:56  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/28/2012 :  09:13:28  Show Profile  Reply with Quote
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

USA
3 Posts

Posted - 12/28/2012 :  09:55:10  Show Profile  Reply with Quote
This works perfectly, thank you!
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/28/2012 :  10:18:32  Show Profile  Reply with Quote
You are welcome
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22742 Posts

Posted - 01/02/2013 :  02:20:47  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000