GeoMaps GeoMaps - 9 months ago 135
SQL Question

How to apply queries inside the same table?

I have very easy question, but I can't seem to figure the answer out.

I have the following table:

declare @Register table (Citizen nvarchar(50), Role nvarchar(10), Process nvarchar(10))

insert @Register values
( 'A', 'seller' , 'Process1' )
, ( 'A', 'seller' , 'Process1' )
, ( 'A', 'seller' , 'Process1' )
, ( 'A', 'seller' , 'Process2' )
, ( 'A', 'buyer' , 'Process3' )
, ( 'A', 'seller' , 'Process3' )
, ( 'B', 'seller' , 'Process3' )
, ( 'B', 'seller' , 'Process4' )
, ( 'C', 'seller' , 'Process4' )


I'm wondering how to extract the Citizens who interact in more than one process as a Seller and the number of different process as Seller. I want also add to the output table the ProcessNumber (for example, if a citizen was a seller in 3 different process it will generate one line for each process, in this case, 3. Showing the Process the person was involved as seller) and the Number of different process this citizen was seller (in this cited example, it'll be 3, because the citizen was a seller in 3 different process)

So the result would be:

Citizen | Process | Number_Of_Diff_Process_as_Seller
-----------+----------+------------------------------------
A | Process1 | 3
A | Process2 | 3
A | Process3 | 3
B | Process3 | 2
B | Process4 | 2

Answer Source
SELECT DISTINCT a.Citizen, a.Process, b.Number_Of_Diff_Process_as_Seller
FROM @Register a
INNER JOIN (SELECT Citizen, COUNT(DISTINCT Process) Number_Of_Diff_Process_as_Seller FROM @Register WHERE Role = 'seller' GROUP BY Citizen) b
ON a.Citizen = b.Citizen
WHERE a.Role = 'seller' AND b.Number_Of_Diff_Process_as_Seller> 1

Can't figure out why do you need the number of processes per citizen on each record, but I guess that's out of scope.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download