Query to get record count

[anonim1]

Below is a snippet of a table I want to query (sorted by user_id, choice, type):

id     user   type choice
 1	12345	1	  1
 2	12345	2	  2
 3	12345	1	  4
 4	12345	2	  4
 5	12345	2	  6
 6	12345	1	  9
 7	12345	2	  9
 8	12345	1	 11
 9	12345	1	 13
10	12345	2	 13
11	67890	1	  1
12	67890	2	  1
13	67890	1	  2
14	67890	2	  3
15	67890	1	  4
16	67890	2	  4
17	67890	1	  7
18	67890	2	  7
19	67890	1	 11
20	67890	2	 13

I want to get a count of all the records where a SINGLE user made the same "choice" for both "type 1" and "type 2".

In the example above, the relevant rows would be:

id     user   type choice
 3	12345	1	  4
 4	12345	2	  4
 6	12345	1	  9
 7	12345	2	  9
 9	12345	1	 13
10	12345	2	 13
11	67890	1	  1
12	67890	2	  1
15	67890	1	  4
16	67890	2	  4
17	67890	1	  7
18	67890	2	  7

So, the record count I would want returned would be 6 (12 is okay too - I can just divide it by two).

 

[gmmastros]

Self Join!

select Count(*)
From   [!]TableName[/!] A
       Inner Join [!]TableName[/!] B 
         On A.UserId = B.UserId
         And A.Type = 1
         And B.Type = 2
         And A.Choice = B.Choice

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AlexCuse]

See if this will do the trick for you? What it does is split your table into two separate derived tables/subqueries, (one for type one, one for type 2), and then joins the two on user and choice.

select count(a.ID)
from
(
select id, [user], type, choice
from myTable 
where Type = 1
) a
inner join
(
select id, [user], type, choice
from myTable
where Type = 2
) b
on a.[User] = b.[User]
and a.Choice = b.Choice

HOpe it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[anonim1]

Self join - very cool!

I was actually thinking how much easier it would be to write the query if the data was in two separate tables before.. This is a really nice (and simple) way of doing it, thanks!