Subquery within subquery?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to group a Customer file by address (to search for duplicates) and for
each address I need a Customer Count and a Fund Count. I know that I can do
this with multiple queries. Can I do it with one? My output should be
something like this:

Address CusCnt FndCnt
123 Main St. 5 2
1234 Second St. 6 6
12345 First St. 2 1

I tried something like this, but can't get it to work:
SELECT Address, Count(Address) as CusCnt,
(select count(*) from
(select distinct fund from customer as c where c.address =
customer.address)) as FndCnt
FROM Customer GROUP BY Address;

Any ideas?
 
THis should help you

SELECT Address, CusCnt, FndCnt
FROM (SELECT Address, Count(*) AS CusCnt FROM Customer GROUP BY Address)
CustomerCount
INNER JOIN (SELECT Address, Sum(Fund) AS FndCnt FROM Customer GROUP BY
Address) CustomerFund
ON CustomerCount.Address=CustomerFund.Address

I made a count of addresses and a sum of the fund field

- Raoul
 
Back
Top