How to Group based on Distinct value of other column

Question

I have this input table

+--------+-----------+------------+----------+
| TaskId | OwnerName | WorkerName | Category |
+--------+-----------+------------+----------+
|      1 | Sara      | Sara       |        1 |
|      1 | Sara      | Maya       |        1 |
|      1 | Sara      | Sara       |        1 |
|      2 | Sara      | Sara       |        0 |
|      2 | Sara      | Sara       |        0 |
|      3 | Sam       | Sam        |        1 |
|      3 | Sam       | Sam        |        1 |
|      3 | Sam       | Sam        |        1 |
|      4 | Ella      | Ella       |        1 |
|      4 | Ella      | Ella       |        1 |
|      5 | Ella      | Ella       |        1 |
|      6 | Ella      | Ella       |        0 |
+--------+-----------+------------+----------+

I want to calculate how many times the owner name has in category column high (1) or low (0).

The tasks should be counted as Distinct , also if a task has owner name != worker name... the entire task will be dropped.

For example, TaskId 1 will be dropped.

Expected Output:

+-----------+------------------+-----------------+-------+------+
| OwnerName | #UniqueHighTasks | #UniqueLowTasks | %High | %Low |
+-----------+------------------+-----------------+-------+------+
| Sara      |                0 |               1 | 0     | 100  |
| Sam       |                1 |               0 | 100   | 0    |
| Ella      |                2 |               1 | 66%   | 33%  |
+-----------+------------------+-----------------+-------+------+

My Attempt isn't taking into consideration that the distinct ids and that task 1 will be dropped, how to accomplish that?

Attempt:

SELECT 
      [OwnerName], 


  (100 * COALESCE(COUNT(CASE
    WHEN [Category] = 1 THEN 1
  END), 0) /
  (COALESCE(COUNT(CASE
    WHEN [Category] = 1 THEN 1
  END), 0) + COALESCE(COUNT(CASE
    WHEN [Category] = 0 THEN 1
  END), 0))) AS %High,

  (100 * COALESCE(COUNT(CASE
    WHEN [Category] = 0 THEN 1
  END), 0) /
  (COALESCE(COUNT(CASE
    WHEN [Category] = 0 THEN 1
  END), 0) + COALESCE(COUNT(CASE
    WHEN [Category] = 1 THEN 1
  END), 0))) AS %Low,

  SUM(case [Category] when 1 then 1 else 0 end) AS '#UniqueHighTasks',
  SUM(case [Category] when 0 then 1 else 0 end) AS '#UniqueLowTasks'


   FROM [dbo].[mytable]
  Group by  [OwnerName]

score 1 · Accepted Answer · answered Apr 17 '18 at 15:58

Try this query:

declare @t table (TaskId int, OwnerName varchar(10), WorkerName varchar(10), Category int)
insert into @t
values 
    (1, 'Sara','Sara', 1), (1, 'Sara','Maya', 1)
    , (1, 'Sara','Sara', 1), (2, 'Sara','Sara', 0)
    , (2, 'Sara','Sara', 0), (3, 'Sam','Sam', 1)
    , (3, 'Sam','Sam', 1), (3, 'Sam','Sam', 1)
    , (4, 'Ella','Ella', 1), (4, 'Ella','Ella', 1)
    , (5, 'Ella','Ella', 1), (6, 'Ella','Ella', 0)

select
    OwnerName, UniqueHighTasks, UniqueLowTasks
    , [%High] = UniqueHighTasks * 100.0 / (UniqueHighTasks + UniqueLowTasks)
    , [%Low] = UniqueLowTasks * 100.0 / (UniqueHighTasks + UniqueLowTasks)
from (
    select
        OwnerName, UniqueHighTasks = count(distinct case when Category = 1 then TaskId end)
        , UniqueLowTasks =  count(distinct case when Category = 0 then TaskId end)
    from
        @t
    where
        TaskId not in (select TaskId from @t where OwnerName <> WorkerName)
    group by OwnerName
) t

score 1 · Answer 2 · answered Apr 17 '18 at 16:26

I think this will do it

declare @T table (TaskId int, OwnerName varchar(20), WorkerName varchar(20), Category int);
insert into @T values
  (1, 'Sara', 'Sara', 1 )
, (1, 'Sara', 'Maya', 1 )
, (1, 'Sara', 'Sara', 1 )
, (2, 'Sara', 'Sara', 0 )
, (2, 'Sara', 'Sara', 0 )
, (3, 'Sam',  'Sam',  1 )
, (3, 'Sam',  'Sam',  1 )
, (3, 'Sam',  'Sam',  1 )
, (4, 'Ella', 'Ella', 1 )
, (4, 'Ella', 'Ella', 1 )
, (5, 'Ella', 'Ella', 1 )
, (6, 'Ella', 'Ella', 0 );
with cte as 
( select distinct t.TaskId, t.OwnerName, t.Category 
  from @T t
  where TaskID not in (select TaskId from @T where OwnerName <> WorkerName)
)
select cte.OwnerName 
     , count(*) taskCount 
     , sum(category) as highCount
     , count(*) - sum(category) as lowCount 
     , 100.0*sum(category)/count(*) as highPct
     , 100.0*(count(*)-sum(category))/count(*) as lowPct
from cte 
group by cte.OwnerName

OwnerName            taskCount   highCount   lowCount    lowPct                                 highPct
-------------------- ----------- ----------- ----------- --------------------------------------- ---------------------------------------
Ella                 3           2           1           66.666666666666                         33.333333333333
Sam                  1           1           0           100.000000000000                        0.000000000000
Sara                 1           0           1           0.000000000000                          100.000000000000

score 0 · Answer 3 · answered Apr 17 '18 at 15:42

0

Take your current query, and instead of querying the table directly, query a CTE or derived table that simply does a SELECT DISTINCT from the table.

answered Apr 17 '18 at 15:42

Tab Alleman

31,483
7
36
52

score 0 · Answer 4 · answered Apr 17 '18 at 15:46

You could try this

WITH nonDup AS (
  SELECT DISTINCT [TaskId], [OwnerName], [WorkerName], [Category] 
  FROM mytable t1
  WHERE NOT EXISTS(SELECT 1 FROM mytable t2 WHERE t1.TaskID = t2.TaskID and t2.OwnerName <> t2.WorkerName)
),
calc AS (
  SELECT 
      OwnerName,
      SUM(Category) AS UniqueHighTasks,
      SUM(CASE WHEN Category = 0 THEN 1 ELSE 0 END) AS UniqueLowTasks
  FROM nonDup
  GROUP BY OwnerName
)
SELECT OwnerName,UniqueHighTasks,UniqueLowTasks,
    CASE WHEN UniqueHighTasks+ UniqueLowTasks<> 0 THEN UniqueHighTasks*1.0/(UniqueHighTasks+ UniqueLowTasks) END AS [%High],
    CASE WHEN UniqueHighTasks+ UniqueLowTasks<> 0 THEN UniqueLowTasks*1.0/(UniqueHighTasks+ UniqueLowTasks) END AS [%Low]
FROM calc

How to Group based on Distinct value of other column

4 Answers4