Help with Sub Query PLEASE!

[airon]

I am trying to create a query with four group by fields and then three derived fields.
Derived fields: Filesize, HoldOver, CallableAccounts

I need to create a query where CallableAccounts will execute for each row of data returned.

In the current query layout, CallableAccounts returns the same number for every row of data.

Is this possible???
Any suggestions.


SELECT L.StratType, L.Site, L.Bucket, L.Cycle, L.FileDate, (Count([AccountNumber])) AS FileSize,
(Count([Accountnumber]))-(Count([ReleaseCode])) AS HoldOver,
(SELECT (Count(AccountNumber)) FROM List_ALL
WHERE
(((ZonePhone)<>'Z' Or (ZonePhone) Is Not Null) AND
((RTStatCode) In ('SC','OL','SK','NC','PD','T')) AND
((HomePhoneFlag) In ('G','U','S') Or (HomePhoneFlag) Is Null) AND ((Bucket)='2'))) AS CallableAccounts
FROM List_ALL as L
GROUP BY L.StratType, L.Site, L.Bucket, L.Cycle, L.FileDate
HAVING L.Bucket=&quot;2&quot;;

 

[r937]

i'm guessing, but try this --

SELECT L.StratType
     , L.Site
     , L.Bucket
     , L.Cycle
     , L.FileDate
     , Count([AccountNumber]) AS FileSize
     , Count([Accountnumber])
      -Count([ReleaseCode])   AS HoldOver
     , Sum(
         case 
           when ( ZonePhone <> 'Z' 
               Or ZonePhone Is Not Null ) 
            and ( RTStatCode In ('SC','OL','SK','NC','PD','T') ) 
            and ( HomePhoneFlag In ('G','U','S') 
               Or HomePhoneFlag Is Null ) 
           then 1 else 0
         end 
           )                  AS CallableAccounts
  FROM List_ALL as L
GROUP 
    BY L.StratType
     , L.Site
     , L.Bucket
     , L.Cycle
     , L.FileDate
 WHERE L.Bucket=&quot;2&quot;

 

[airon]

Hmm, I think there are few glitches, because that is not seeming to work either
thanks for the feedback.