# Thread: Problem with complex query

1. Registered User
Join Date
Feb 2004
Posts
32

## Unanswered: Problem with complex query

Hello!

I have a query that queries two tables (including a self join on one of them) and returns a result set that almost (but not quite) gives me what I want , and was wondering if someone could give me some pointers. Rather than show the whole query (complex), I'll show the result set and describe what i want:

Tab1.efID Tab1.VID Tab2.efID Tab2.VID
\$00046342 7 \$00046342 8
\$00046342 7 \$00046342 19
\$00046342 18 \$00046342 19

I want to amend the query so that it returns a count of the distinct rows of Tab1.efID,Tab1.VID - from the above result set, it should return just a count of the first and third rows, i.e 2

The statement SELECT DISTINCT Tab1.efID,Tab1.VID would return the two rows, but obviously SELECT COUNT(DISTINCT Tab1.efID,Tab1.VID) doesn't work.

SELECT COUNT(DISTINCT Tab1.efID + CAST(Tab1.VID AS VARCHAR(2))) does work, but i thought perhaps there may be a more elegant solution - anyone have any pointers?

Cheers
Greg

2. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
GROUP BY

-PatP

3. Registered User
Join Date
Feb 2004
Posts
492
Pat: I think he goes for a count of unique rows instead of the unique rows alone.

4. Registered User
Join Date
Feb 2004
Posts
492
Also not so elegant:
select distinct Col1, Col2.....
select @@rowcount

perhaps it's possbile to add a computed column that merges Col1 and Col2 as you did, making the count distinct on a single column possible.

EDIT: or:
select count(*)
from (select distinct Col1, Col2) t1

But I can't think of a really neat solution though.
Last edited by Kaiowas; 08-20-04 at 13:32.

5. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Originally Posted by Kaiowas
Pat: I think he goes for a count of unique rows instead of the unique rows alone.
You could use Count(*) with GROUP BY, or am I missing something? Even if he just wanted a count of the number of groups, he could create a virtual table using GROUP BY and then count the number of rows in that table, something like:
Code:
```SELECT Count(*)
FROM (SELECT a.Efid, a.VID
FROM myTable AS a
GROUP BY a.Efid, a.VID) AS b```
-PatP

6. Registered User
Join Date
Feb 2004
Posts
32
Thanks guys,
I'll try them out.
Greg

#### Posting Permissions

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