CODEWITHSUNDEEP
mysqlsqlms-access
Scatman_John
Scatman_John

Reputation: 107

Counting unique entries in access

I'm writing queries in access. I'm having problems with one where I want to count with distinct, but I have found out that it's not supported in access. I have looked at examples with sub queries but I don't get it and can't get it to work. Here is my original query: 

SELECT Stad,COUNT(DISTINCT Biografer.BiograferID) AS Biografer ,COUNT(SalongsID) AS Salonger
FROM Biograf,Salong
WHERE Salong.BiografID=Biograf.BiografID
GROUP BY Stad

Any help appreciated! Thanks in advance.

Upvotes: 0

Views: 76

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

First, you should learn proper join syntax. Second, you can do this with nested subqueries:

SELECT Stad, COUNT(*) as NumBiorafer, SUM(Salonger) as SumSalonger
FROM (SELECT Stad, Biografer.BiograferID, COUNT(*) AS Salonger
      FROM Biograf INNER JOIN
           Salong
           ON Salong.BiografID = Biograf.BiografID
      GROUP BY Stad, Biografer.BiograferID
     ) as sb
GROUP BY Stad;

Upvotes: 1

Related Questions