[AccessD] ORA-00937: not a single-group group function

Paul

I think that you need a  "Group By" expression.
"Group by t1.mai_fb_no" or "group by Bills";
I don't remember which it wants ( field name
or alias ).

When using aggriate functions ( such as count
or sum ) there needs to be a "Group By"
section and all fields not summed need to
be listed in it.

Dave



  ----- Original Message ----- 
  From: Gustav Brock 
  To: accessd at databaseadvisors.com 
  Sent: Saturday, May 10, 2003 4:32 AM
  Subject: Re: [AccessD] ORA-00937: not a single-group group function


  Hi Paul

  Not having an Oracle instance running, I just wonder if you can omit
  "distinct" from the first line?

  /gustav


  > 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
  > field in the same sql query using a subquery.  

  > Here is a sample output.
  > Filtered = 30 records
  > All (unfiltered) 100 records

  > Bills, Wt, LbClass, WtdClass, TotalBills
  > 30, .., .., .., 100

  > Can anyone please help!
  > Thanks,
  > Paul Millard


  > select distinct
  > Count(Distinct t1.mai_fb_no) Bills,
  > Sum(t2.mai_fb_item_weight) Wt,
  > Sum(t2.mai_fb_item_weight*t3.dw_freight_class_id) LbClass,
  > Sum(t2.mai_fb_item_weight*t3.dw_freight_class_id)/Sum(t2.mai_fb_item_weight) WtdClass,
  > /*begin subquery*/
  > (select 
  > Count(t1.mai_fb_no) Bills
  > from
  > mai_shipment t1,
  > dw_customer t4,
  > dw_date t5
  > where
  > t1.mai_shipper_key=t4.dw_cust_key and
  > t1.mai_dl_date_key=t5.dw_date_key and
  > t4.dw_cust_old_number='SNM2904SFR' and
  > t5.dw_day_date between to_date('2003-03-04 00:00:00', 'YYYY-MM-DD HH24:MI:SS') and 
  > to_date('2003-03-04 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) As TotalBills
  > /*end subquery*/
  > from
  > mai_shipment t1,
  > mai_shipment_item t2,
  > dw_freight_class t3,
  > dw_customer t4,
  > dw_date t5
  > where
  > t1.mai_fb_no=t2.mai_fb_no and
  > t1.mai_fb_suf=t2.mai_fb_suf and
  > t2.mai_freight_class_key=t3.dw_freight_class_key and
  > t1.mai_shipper_key=t4.dw_cust_key and
  > t1.mai_dl_date_key=t5.dw_date_key and
  > t2.mai_fb_item_weight>0 and
  > t3.dw_freight_class_id Is Not Null and
  > t1.mai_fb_no In('747938850', '936046135') and
  > t5.dw_day_date between to_date('2003-03-04 00:00:00', 'YYYY-MM-DD HH24:MI:SS') and 
  > to_date('2003-03-04 00:00:00', 'YYYY-MM-DD HH24:MI:SS')

  _______________________________________________
  AccessD mailing list
  AccessD at databaseadvisors.com
  http://databaseadvisors.com/mailman/listinfo/accessd
  Website: http://www.databaseadvisors.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030510/82330c00/attachment-0001.html>