SQL-How to select distinct rows that have same value in 2 columns

Question

I have this input table

+--------+---------+---------+-------+-----------+
| TaskId | member1 | member2 | score | functions |
+--------+---------+---------+-------+-----------+
|      1 | Jack    | Jack    |   100 | marketing |
|      1 | Jack    | Jack    |   100 | marketing |
|      2 | Jack    | Steve   |    90 | interior  |
|      2 | Jack    | Steve   |    90 | interior  |
|      3 | Steve   | Jack    |    70 | program   |
|      3 | Steve   | Jack    |    70 | program   |
|      4 | Jack    | Mia     |    30 | develop   |
|      4 | Jack    | Mia     |    30 | develop   |
|      5 | Mia     | Jack    |    20 | interior  |
|      5 | Mia     | Jack    |    20 | interior  |
+--------+---------+---------+-------+-----------+

I calculated 2 things

• UniqueHighTasks :Distinct #Of tasks that (for eg:) Jack existed in score(75-100) whether he is in member 1 or 2
• UniqueLowTasks :Distinct #Of tasks that (for eg:) Jack existed in score(0-100) whether he is in member 1 or 2
• I excluded if member1 = member2 for that calculation

Now, I want to count the distinct number of functions that Jack exists in whether he is in member1 or member2

• For example: Jack as in member1 is in (interior,develop)

• Jack as in member 2 is in (program, interior)

The count of my final result is 4 which is wrong, how can i get The distinct count which is 3 for Jack which are (interior,develop,program) , the same for the rest of the names.

SQL Fiddle Code

Answer 1

Instead of applying your logic for individual sets, you can combine both the sets together using UNION ALL and apply you logic at a single place.

SELECT member, 
       uniquehightasks, 
       uniquelowtasks, 
       [%High] = uniquehightasks * 100.0 / ( uniquehightasks + uniquelowtasks ), 
       [%Low] = uniquelowtasks * 100.0 / ( uniquehightasks + uniquelowtasks ), 
       functions 
FROM   (SELECT member, 
               Sum(uniquehightasks) AS 'UniqueHighTasks', 
               Sum(uniquelowtasks)  AS 'UniqueLowTasks', 
               Sum(functions)       AS 'functions' 
        FROM   (SELECT member, 
                       UniqueHighTasks = Count(DISTINCT CASE WHEN score >= 75 THEN [taskid] END), 
                       UniqueLowTasks = Count(DISTINCT CASE  WHEN score < 75 THEN  [taskid] END), 
                       functions=Count(DISTINCT functions) 
                FROM   (SELECT [taskid], member1 AS Member, functions, score 
                        FROM   mytable 
                        WHERE  member1 != member2 
                        UNION ALL
                        SELECT [taskid], member2 AS member,functions, score 
                        FROM   mytable 
                        WHERE  member1 != member2) t22 
                GROUP  BY member)t3 
        GROUP  BY t3.member) t4 

Output

+--------+-----------------+----------------+-----------------+------------------+-----------+
| member | UniqueHighTasks | UniqueLowTasks | %High           | %Low             | functions |
+--------+-----------------+----------------+-----------------+------------------+-----------+
| Jack   | 1               | 3              | 25.000000000000 | 75.000000000000  | 3         |
+--------+-----------------+----------------+-----------------+------------------+-----------+
| Mia    | 0               | 2              | 0.000000000000  | 100.000000000000 | 2         |
+--------+-----------------+----------------+-----------------+------------------+-----------+
| Steve  | 1               | 1              | 50.000000000000 | 50.000000000000  | 2         |
+--------+-----------------+----------------+-----------------+------------------+-----------+

DEMO

Answer 2

The reason you are getting count as 4 for Jack is bacause both part of your UNION are giving you a count 2 and then you are doing a sum of it.

Try this, but this will also give count 2 for Mia instead of 1.

http://sqlfiddle.com/#!18/49461/10

SELECT member1, 
       uniquehightasks, 
       uniquelowtasks, 
       [%High] = uniquehightasks * 100.0 / ( uniquehightasks + uniquelowtasks ), 
       [%Low] = uniquelowtasks * 100.0 / ( uniquehightasks + uniquelowtasks ), 
       functions 
FROM   (SELECT member1, 
               Sum(uniquehightasks)      AS 'UniqueHighTasks', 
               Sum(uniquelowtasks)       AS 'UniqueLowTasks', 
               Count(DISTINCT functions) AS 'functions' 
        FROM   (SELECT member1, 
                       UniqueHighTasks = Count(DISTINCT CASE 
                                                          WHEN score >= 75 THEN 
                                                          [taskid] 
                                                        END), 
                       UniqueLowTasks = Count(DISTINCT CASE 
                                                         WHEN score < 75 THEN 
                                                         [taskid] 
                                                       END), 
                       functions 
                FROM   mytable 
                WHERE  member1 != member2 
                GROUP  BY member1, 
                          functions 
                UNION 
                SELECT member2, 
                       UniqueHighTasks = Count(DISTINCT CASE 
                                                          WHEN score >= 75 THEN 
                                                          [taskid] 
                                                        END), 
                       UniqueLowTasks = Count(DISTINCT CASE 
                                                         WHEN score < 75 THEN 
                                                         [taskid] 
                                                       END), 
                       functions 
                FROM   mytable 
                WHERE  member1 != member2 
                GROUP  BY member2, 
                          functions)t3 
        GROUP  BY t3.member1) t4