[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')