<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2800.1106" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT size=2>Paul</FONT></DIV>
<DIV><FONT size=2></FONT> </DIV>
<DIV><FONT size=2><FONT color=#ff0000><STRONG>I think</STRONG></FONT> that you
need a "Group By" expression.</FONT></DIV>
<DIV><FONT size=2>"Group by t1.mai_fb_no" or "group by Bills";</FONT></DIV>
<DIV><FONT size=2>I don't remember which it wants ( field name</FONT></DIV>
<DIV><FONT size=2>or alias ).</FONT></DIV>
<DIV><FONT size=2></FONT> </DIV>
<DIV><FONT size=2>When using aggriate functions ( such as count</FONT></DIV>
<DIV><FONT size=2>or sum ) there needs to be a "Group By"</FONT></DIV>
<DIV><FONT size=2>section and all fields not summed need to</FONT></DIV>
<DIV><FONT size=2>be listed in it.</FONT></DIV>
<DIV><FONT size=2></FONT> </DIV>
<DIV><FONT size=2>Dave</FONT></DIV>
<DIV><FONT size=2></FONT> </DIV>
<DIV><FONT size=2></FONT> </DIV>
<DIV><FONT size=2></FONT> </DIV>
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV
style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>From:</B>
<A title=gustav@cactus.dk href="mailto:gustav@cactus.dk">Gustav Brock</A>
</DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A title=accessd@databaseadvisors.com
href="mailto:accessd@databaseadvisors.com">accessd@databaseadvisors.com</A>
</DIV>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Saturday, May 10, 2003 4:32
AM</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> Re: [AccessD] ORA-00937: not a
single-group group function</DIV>
<DIV><BR></DIV>Hi Paul<BR><BR>Not having an Oracle instance running, I just
wonder if you can omit<BR>"distinct" from the first
line?<BR><BR>/gustav<BR><BR><BR>> I'm receiving the error above when doing
this query. I'm trying to count and sum a few fields (with filter on account
level and fb_no) while putting the total (filtered to account level) in
another<BR>> field in the same sql query using a subquery.
<BR><BR>> Here is a sample output.<BR>> Filtered = 30 records<BR>>
All (unfiltered) 100 records<BR><BR>> Bills, Wt, LbClass, WtdClass,
TotalBills<BR>> 30, .., .., .., 100<BR><BR>> Can anyone please
help!<BR>> Thanks,<BR>> Paul Millard<BR><BR><BR>> select
distinct<BR>> Count(Distinct t1.mai_fb_no) Bills,<BR>>
Sum(t2.mai_fb_item_weight) Wt,<BR>>
Sum(t2.mai_fb_item_weight*t3.dw_freight_class_id) LbClass,<BR>>
Sum(t2.mai_fb_item_weight*t3.dw_freight_class_id)/Sum(t2.mai_fb_item_weight)
WtdClass,<BR>> /*begin subquery*/<BR>> (select <BR>>
Count(t1.mai_fb_no) Bills<BR>> from<BR>> mai_shipment t1,<BR>>
dw_customer t4,<BR>> dw_date t5<BR>> where<BR>>
t1.mai_shipper_key=t4.dw_cust_key and<BR>>
t1.mai_dl_date_key=t5.dw_date_key and<BR>>
t4.dw_cust_old_number='SNM2904SFR' and<BR>> t5.dw_day_date between
to_date('2003-03-04 00:00:00', 'YYYY-MM-DD HH24:MI:SS') and <BR>>
to_date('2003-03-04 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) As TotalBills<BR>>
/*end subquery*/<BR>> from<BR>> mai_shipment t1,<BR>>
mai_shipment_item t2,<BR>> dw_freight_class t3,<BR>> dw_customer
t4,<BR>> dw_date t5<BR>> where<BR>> t1.mai_fb_no=t2.mai_fb_no
and<BR>> t1.mai_fb_suf=t2.mai_fb_suf and<BR>>
t2.mai_freight_class_key=t3.dw_freight_class_key and<BR>>
t1.mai_shipper_key=t4.dw_cust_key and<BR>>
t1.mai_dl_date_key=t5.dw_date_key and<BR>> t2.mai_fb_item_weight>0
and<BR>> t3.dw_freight_class_id Is Not Null and<BR>> t1.mai_fb_no
In('747938850', '936046135') and<BR>> t5.dw_day_date between
to_date('2003-03-04 00:00:00', 'YYYY-MM-DD HH24:MI:SS') and <BR>>
to_date('2003-03-04 00:00:00', 'YYYY-MM-DD
HH24:MI:SS')<BR><BR>_______________________________________________<BR>AccessD
mailing list<BR><A
href="mailto:AccessD@databaseadvisors.com">AccessD@databaseadvisors.com</A><BR><A
href="http://databaseadvisors.com/mailman/listinfo/accessd">http://databaseadvisors.com/mailman/listinfo/accessd</A><BR>Website:
<A
href="http://www.databaseadvisors.com">http://www.databaseadvisors.com</A><BR></BLOCKQUOTE></BODY></HTML>