Adding an Unrealted Column to a Cross

[fryguy5049]

I have crosstab query that uses a where clause to limit the data retrieved. Is there a way to add an additional column to the crosstab that isn't limited by the where clause?
Ideally I would like the results of this query as a column in the main query:

'select accstore.store ,count(date)
from store_stat
left outer join accstore on store_stat.store_number = accstore.storenum
where store_stat.super_stat='ia'
group by store
order by district,store"

Here is the main query:
select accstore.store , accstore.district,sum(cd71.amt) ,count(distinct cd71.acct) ,sum(cd71.amt)/count(distinct cd71.acct),sum(store_stat.credit_line),count(store_stat.super_stat)
from cd71, store_stat
left join accstore on accstore.merch_num=cd71.store
where store_stat.date ='2005-03-07' and cd71.acct = store_stat.acctno and cd71.date = store_stat.date
and cd71.tran = '253'
group by accstore.store
order by district,store;
Using MySQL 4.0

 

[r937]

select accstore.store 
     , accstore.district
     , sum(cd71.amt) 
     , count(distinct cd71.acct) 
     , sum(cd71.amt)
      /count(distinct cd71.acct)
     , sum(store_stat.credit_line)
     , count(store_stat.super_stat)
     , ( select count(super_stat) 
           from store_stat 
          where store_number = accstore.storenum 
            and date = cd71.date
            and super_stat = 'ia'
       ) as super_stat_count
  from cd71
inner
  join store_stat 
    on cd71.acct = store_stat.acctno 
   and cd71.date = store_stat.date 
left outer
  join accstore 
    on cd71.store = accstore.merch_num 
 where cd71.date = '2005-03-07' 
   and cd71.tran = '253' 
group 
    by accstore.store
order 
    by district
     , store;

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)