query to count number of different values?

Question

I have a table in MS Access 2003 that looks like this:

    *url            id*
    example.com    red
    example.com    blue
    example.com    blue
    other.com      red
    other.com      orange
    more.com       blue

For each URL I want to know how many unique id there are. so in this case the result should be:

    *url             count of distinct id*
    example.com          2           (because red and blue are the only values)
    other.com            2
    more.com             1

This is very similar to SQL query to count number of different values except the solution in that case does not work because it relies on COUNT DISTINCT, but that is not supported in Access. I have tried to look up alternative ways of doing count distinct in Access but I'm afraid I don't understand the answers.

So I guess this problem can be summarized as "how to simulate count distinct in msaccess".

I would greatly appreciate it if someone can give me a hint.

+1 For a well phrased question (and searching the archives for answers first! :). — Leigh
– Leigh, Commented Dec 13, 2012 at 1:49

Taryn · Accepted Answer · 2012-12-13 01:47:51Z

5

This should work in MS Access 2003 (I just tested it):

SELECT url, count(id) as CountOfId
FROM
(
   SELECT distinct id, url
   FROM yourtable
) x
GROUP BY url

In this you get the distinct id and url in a subquery and then you count that result.

answered Dec 13, 2012 at 1:47

Taryn

249k57 gold badges375 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Raymond Over a year ago

Thanks very much, that does work. I removed the "x" which I believe is a typo (?) and it does the job great. I appreciate your help.

Taryn Over a year ago

The x was an alias for the subquery. I am used to SQL server which requires it, but it did work in access with the alias

Leigh · Accepted Answer · 2012-12-13 02:00:02Z

1

Here is another way of doing it without using the distinct keyword. (Although it looks like later versions of access would support it):

SELECT t.url, Count(*) AS distinct_id_count
FROM
(
    SELECT url
    FROM *source_table_name_here*
    GROUP BY url, id
) AS t
GROUP BY t.url
ORDER BY COUNT(*) DESC, t.url;

edited Dec 13, 2012 at 2:00

Leigh

28.9k10 gold badges58 silver badges109 bronze badges

answered Dec 13, 2012 at 1:55

DWright

9,5594 gold badges39 silver badges57 bronze badges

1 Comment

Raymond Over a year ago

Thank you, I will try this if I have any issues with the previous solution.

Collectives™ on Stack Overflow

query to count number of different values?

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related