• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 719
  • Last Modified:

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.
0
hologosesh
Asked:
hologosesh
1 Solution
 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now