I think you can just omitt the actionID from your group by clause and
you should be set.
Both the cout and the sum are aggregates so only return 1 value,
grouped by the values in the group by clause.
HTH
- Original Message -
From: Russ [EMAIL PROTECTED]
Date: Fri, 17 Sep 2004 22:20:28
Jocheem (My apologies if I murdered your name) is much better at this. But
I think I've learned from him, you need to do something like:
SELECT Agency_No AS outerNO, Fund_No, count(*) as totalRecs,
(SELECT COUNT(Completed)
FROM
Here is your best bet:
Run this query:
SELECT Agency_No, Fund_No, completed, count(*) as totalRecs,
FROM SCO567_568
WHERE (Agency_No = #client.agency_no#)
GROUP BY Agency_No, Fund_No, completed
ORDER BY Agency_No, Fund_No, completed
You'll get results like this:
AGY FUND
Andrew Peterson wrote:
SELECT Agency_No, Fund_No, count(*) as totalRecs,
(SELECT COUNT(Completed)
FROM sco567_568
WHERE Completed = 1 AND AGENCY_NO
=#client.Agency_No#) AS totCompleted
Hi Andrew... To get your subgrouping, you need to alias your table... so... instead of
for instance
select x,y,count(z), (select count(z) from mytable where z = 1 and x = #var#) from
mytable group by x,y
you would want something like this:
select my.x,my.y,count(my.z), (select count(my.z)
Jochem van Dieten wrote:
SELECT
Agency_No,
Fund_No,
count(1) as totalRecs,
(
SELECT COUNT(1)
FROMsco567_568 b
WHERE Completed = 1
AND a.AGENCY_NO = a.AGENCY_NO
FANTASTIC. Thank you all.
Sincerely,
Andrew
-Original Message-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Monday, July 28, 2003 1:37 PM
To: CF-Talk
Subject: Re: [sql] grouping
Jochem van Dieten wrote
7 matches
Mail list logo