How do I sum a count, access db, sql query

I use the query below to tell me how many unique custName entries there are for each location.

SELECT Location, Team, sum(EffectiveRevenueReal), COUNT(custName) AS DistinctNo From vwResults6 WHERE ressource=0 group by Location, Team ORDER BY COUNT(custName) DESC

I now need to sum a grand total of all of the unique custName counts.  I have tried
SELECT SUM(c) FROM (SELECT Location, ressource, Count(custName) AS c FROM vwResults6 WHERE ressource=0 GROUP BY Location, ressource) As b

However, when I try to print b, I get the error:
Item cannot be found in the collection corresponding to the requested name or ordinal.
hologoseshAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the "as b" names the subquery, and not the sum(c) expression...
SELECT SUM(c) as b FROM (SELECT Location, ressource, Count(custName) AS c FROM vwResults6 WHERE ressource=0 GROUP BY Location, ressource) As subquery

Open in new window

0
 
hologoseshAuthor Commented:
Great, thanks.  Works perfectly.  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.