Millard, Paul --- Sr. Developer Analyst ---WGO
Paul.Millard at freight.fedex.com
Fri May 9 16:06:47 CDT 2003
Figured out my problem....need to wrap aggregate function around the subquery such as:
Min((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'))) Total Bills
-----Original Message-----
From: Millard, Paul --- Sr. Developer Analyst ---WGO
Sent: Friday, May 09, 2003 1:45 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] ORA-00937: not a single-group group function
Folks,
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')
-----Original Message-----
From: Charlotte Foust [mailto:cfoust at infostatsystems.com]
Sent: Friday, May 09, 2003 1:25 PM
To: accessd at databaseadvisors.com
Subject: RE: Subjects in Newsletters: was RE: [AccessD] SQL in-line
subquery
Are you talking to me?
666
-----Original Message-----
From: Wortz, Charles [mailto:CWortz at tea.state.tx.us]
Sent: Friday, May 09, 2003 11:22 AM
To: accessd at databaseadvisors.com
Subject: RE: Subjects in Newsletters: was RE: [AccessD] SQL in-line
subquery
Cabals are not always satanic! However, I do not know if that is true
of all members of this list. <grin>
Charles Wortz
Software Development Division
Texas Education Agency
1701 N. Congress Ave
Austin, TX 78701-1494
512-463-9493
CWortz at tea.state.tx.us
-----Original Message-----
From: Don Elliker [mailto:delliker at hotmail.com]
Sent: Friday 2003 May 09 14:14
To: accessd at databaseadvisors.com
Subject: RE: Subjects in Newsletters: was RE: [AccessD] SQL in-line
subquery
I was gonna get cabal but I decided on satanlite instead
_d
"Things are only free to the extent that you don't pay for them."
>From: "Wortz, Charles"
>Reply-To: accessd at databaseadvisors.com
>To:
>Subject: RE: Subjects in Newsletters: was RE: [AccessD] SQL in-line
subquery
>Date: Fri, 9 May 2003 12:58:25 -0500
>
>Wrong John! It's JC that revealed the secrets of the cabal.
>
>Charles Wortz
>-----Original Message-----
>From: The Cabal [mailto:john at winhaven.net]
>Sent: Friday 2003 May 09 12:53
>To: accessd at databaseadvisors.com
>Subject: RE: Subjects in Newsletters: was RE: [AccessD] SQL in-line
>subquery
>
>
>unsubscribe AccessD John Bartow "the troublemaker"
>unsubscribe DevAccess John Bartow "the troublemaker" unsubscribe Access
>Unlimited John Bartow "the troublemaker" unsubscribe Inside Microsoft
>Access John Bartow "the troublemaker" unsubscribe LangaList John Bartow
>"the troublemaker" unsubscribe Access Developer News John Bartow "the
>troublemaker" unsubscribe AppTrix John Bartow "the troublemaker"
>unsubscribe Smart Access eXTRA John Bartow "the troublemaker"
>unsubscribe Woody's Access Watch John Bartow "the troublemaker"
>
>
>Let this be a warning to you all.
>
>The Newsletter Cabal
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
**********************************************************
This message contains information that is confidential
and proprietary to FedEx Freight or its affiliates.
It is intended only for the recipient named and for
the express purpose(s) described therein.
Any other use is prohibited.
****************************************************************
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com