# Thread: Counting Problem

1. Registered User
Join Date
Jul 2002
Location
Island of Dots
Posts
316

## Unanswered: Counting Problem

Let's say I have the following two tables:
Code:
```Table 1                 Table 2
ID  COL1    COL2        ID  COL1    COL2
1   1000    A           1   1002    A
2   1001    B           2   1000    A
3   1000    A           3   1001    B```
I want to be able to count the number of unqiue occurances of COL1 in both tables where COL2 = 'A'. The solution I came up with is as follows:

Code:
```SELECT DISTINCT COL1 FROM Table1 WHERE COL2='A' GROUP BY COL1
UNION SELECT DISTINCT COL1 FROM Table2 WHERE COL2='A' GROUP BY COL1```
I then simply look at the rs.RecordCount to give me the number of unique COL1 values accros both tables. Is there a way to actually get the query itself to return the value itself, rather than having to use RecordCount? And, which method would be considered best? Mine, or having the query return the result?

I tried this way:
Code:
```SELECT DISTINCT Count(COL1) AS Cnt
FROM Table1
WHERE (COL2='A');```
But it gives only a count of each COL1 item, rather than each unique item.
Last edited by bcass; 05-11-07 at 10:01.

2. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Do you ONLY want the count or do you want the actual values too? If just the count then a query.

3. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Also, this
Code:
```SELECT DISTINCT COL1 FROM Table1 WHERE COL2='A' GROUP BY COL1
UNION SELECT DISTINCT COL1 FROM Table2 WHERE COL2='A' GROUP BY COL1```

can be shortened to this, which is likely to be more efficient
Code:
``` SELECT COL1 FROM Table1 WHERE COL2='A'
UNION SELECT COL1 FROM Table2 WHERE COL2='A'```

4. Registered User
Join Date
Jul 2002
Location
Island of Dots
Posts
316
I just want the count. So, given the example tables above, a count of unique COL1s where COL2 = A would give a count of 2.

5. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Originally Posted by bcass
I just want the count. So, given the example tables above, a count of unique COL1s where COL2 = A would give a count of 2.
Not 3?????

6. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Anyhoo:

Code:
```
SELECT COUNT (*) AS no_of_distinct_values
FROM (SELECT COL1 FROM Table1 WHERE COL2='A'
UNION SELECT COL1 FROM Table2 WHERE COL2='A') AS dist_cols```

7. Registered User
Join Date
Jul 2002
Location
Island of Dots
Posts
316
Originally Posted by pootle flump
Not 3?????
No. Where COL2 = A, the total unique COL1 values is 2 (1000 and 1002).

8. Registered User
Join Date
Jul 2002
Location
Island of Dots
Posts
316
Originally Posted by pootle flump
Anyhoo:

Code:
```
SELECT COUNT (*) AS no_of_distinct_values
FROM (SELECT COL1 FROM Table1 WHERE COL2='A'
UNION SELECT COL1 FROM Table2 WHERE COL2='A') AS dist_cols```
Thanks for that. It seems obvious now that I see it. At least I was almost there with my original idea!

So, which method would be considered most optimal? Yours, which returns the value, or mine, where you could get the value from a RecordCount?

9. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Originally Posted by bcass
No. Where COL2 = A, the total unique COL1 values is 2 (1000 and 1002).
Don't forget 1001

10. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Mine. ESPECIALLY if you are using a client\server set up

11. Registered User
Join Date
Jul 2002
Location
Island of Dots
Posts
316
Originally Posted by pootle flump
Don't forget 1001
1001 Has COL2 = B, not A!

12. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Originally Posted by bcass
1001 Has COL2 = B, not A!
.

13. Registered User
Join Date
Jul 2002
Location
Island of Dots
Posts
316
No matter! Thanks a lot for your help. Much appreciated.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•