Counts rows returned by query that uses distinct

Question

I have a simple query in this form:

SELECT DISTINCT a, b, c
FROM MyTable
WHERE a = SomeConditionsEtc

etc...

But I need to know how many rows it's going to return. Initially I was doing this:

SELECT COUNT(DISTINCT a)
FROM MyTable
WHERE a = SomeConditionsEtc

But that's not reliable in case a contains duplicates where the other don't. So now I'm using a nested query:

SELECT COUNT(*)
FROM (SELECT DISTINCT a, b, c
      FROM MyTable
      WHERE a = SomeConditionsEtc) AS Temp

Is that the correct approach or is there a better way?

Have you looked at the following SO post already? http://stackoverflow.com/questions/1471250/counting-distinct-over-multiple-columns — Sam, Jun 07 '13 at 13:45
@Sam No I haven't seen that yet, thanks. Well I guess the nested query is the simplest solution then. — Dan, Jun 07 '13 at 14:54

score 1 · Accepted Answer · answered Jun 07 '13 at 15:38

Your query is straight to the point, does the job, and it's simple enough, I'm sure you can bake some unnecessary rocket science into it, but would be overblown imho. Aside from what you have, you can use a group by like below to illustrate what I mean, but you will be basically doing the same thing, getting the uniques and counting them.

SELECT COUNT(1)
FROM (SELECT a
        FROM MyTable
        WHERE a = 'a'
        GROUP BY a, b, c) Temp

Counts rows returned by query that uses distinct

1 Answers1