CODEWITHSUNDEEP
sqlsql-servercountsubquerydistinct
Amy
Amy

Reputation: 641

SQL - Create column with distinct count of another column

I am trying to add in 3 columns to my SQl table.

Column1: Total_Hours_Overall

I want this column to sum the total hours per sequence_ID

Column 2: Total_No_Codes

I want this column to be a count of the distinct values in the 'Client' column.

Column 3: Total Clients

I want this column to be a count of the distinct values in the 'Client' column where the 'Job_Type' column is equal to 'Client'

The below is a snippet of the table I currently have. There are over 800 unique sequence_ID's which have multiple rows per ID. I want the above calculated columns to display the values for each row.

Sequence_ID |Date_European  |Hours  |Month  |Day    |Year   |Day_of_Week    |Client_Number  |Client         |Job_No |Job_Type
1001        |01/09/2017     |7.3    |9      |1      |2017   |Friday         |0              |ANNUAL LEAVE   |0      |ANNUAL LEAVE
1001        |04/09/2017     |7.3    |9      |4      |2017   |Monday         |0              |ANNUAL LEAVE   |0      |ANNUAL LEAVE
1001        |09/08/2017     |2      |8      |9      |2017   |Wednesday      |1              |Admin          |A1     |Non-Billable
1001        |24/08/2017     |1.3    |8      |24     |2017   |Thursday       |2              |Client1        |A2     |Client
1001        |28/08/2017     |2.3    |8      |28     |2017   |Monday         |2              |Client1        |A2     |Client
1001        |16/08/2017     |0.5    |8      |16     |2017   |Wednesday      |3              |Client2        |A3     |Client
1001        |16/08/2017     |1      |8      |16     |2017   |Wednesday      |2              |Client1        |A2     |Client
1001        |18/08/2017     |3      |8      |18     |2017   |Friday         |3              |Client2        |A3     |Client
1001        |22/08/2017     |0.7    |8      |22     |2017   |Tuesday        |4              |Client3        |A4     |Client
1001        |16/08/2017     |7.3    |8      |16     |2017   |Wednesday      |5              |Client4        |A5     |Client
1001        |18/08/2017     |1.3    |8      |18     |2017   |Friday         |5              |Client4        |A5     |Client
1001        |21/08/2017     |1      |8      |21     |2017   |Monday         |5              |Client4        |A5     |Client
1001        |12/09/2017     |0.6    |9      |12     |2017   |Tuesday        |5              |Client4        |A5     |Client
1002        |01/09/2017     |7.3    |9      |1      |2017   |Friday         |0              |ANNUAL LEAVE   |0      |ANNUAL LEAVE
1002        |04/09/2017     |7.3    |9      |4      |2017   |Monday         |0              |ANNUAL LEAVE   |0      |ANNUAL LEAVE
1002        |09/08/2017     |2      |8      |9      |2017   |Wednesday      |1              |Admin          |A1     |Non-Billable
1002        |24/08/2017     |1.3    |8      |24     |2017   |Thursday       |4              |Client3        |A4     |Client
1002        |28/08/2017     |2.3    |8      |28     |2017   |Monday         |5              |Client4        |A5     |Client

Below is how I want my table to look.

Sequence_ID |Date_European  |Hours  |Month  |Day    |Year   |Day_of_Week    |Client_Number  |Client         |Job_No |Job_Type       |Total_Hours_Overall    |Total_No_Codes |Total Clients
1001        |01/09/2017     |7.3    |9      |1      |2017   |Friday         |0              |ANNUAL LEAVE   |0      |ANNUAL LEAVE   |35.6                   |6              |4
1001        |04/09/2017     |7.3    |9      |4      |2017   |Monday         |0              |ANNUAL LEAVE   |0      |ANNUAL LEAVE   |35.6                   |6              |4
1001        |09/08/2017     |2      |8      |9      |2017   |Wednesday      |1              |Admin          |A1     |Non-Billable   |35.6                   |6              |4
1001        |24/08/2017     |1.3    |8      |24     |2017   |Thursday       |2              |Client1        |A2     |Client         |35.6                   |6              |4
1001        |28/08/2017     |2.3    |8      |28     |2017   |Monday         |2              |Client1        |A2     |Client         |35.6                   |6              |4
1001        |16/08/2017     |0.5    |8      |16     |2017   |Wednesday      |3              |Client2        |A3     |Client         |35.6                   |6              |4
1001        |16/08/2017     |1      |8      |16     |2017   |Wednesday      |2              |Client1        |A2     |Client         |35.6                   |6              |4
1001        |18/08/2017     |3      |8      |18     |2017   |Friday         |3              |Client2        |A3     |Client         |35.6                   |6              |4
1001        |22/08/2017     |0.7    |8      |22     |2017   |Tuesday        |4              |Client3        |A4     |Client         |35.6                   |6              |4
1001        |16/08/2017     |7.3    |8      |16     |2017   |Wednesday      |5              |Client4        |A5     |Client         |35.6                   |6              |4
1001        |18/08/2017     |1.3    |8      |18     |2017   |Friday         |5              |Client4        |A5     |Client         |35.6                   |6              |4
1001        |21/08/2017     |1      |8      |21     |2017   |Monday         |5              |Client4        |A5     |Client         |35.6                   |6              |4
1001        |12/09/2017     |0.6    |9      |12     |2017   |Tuesday        |5              |Client4        |A5     |Client         |35.6                   |6              |4
1002        |01/09/2017     |7.3    |9      |1      |2017   |Friday         |0              |ANNUAL LEAVE   |0      |ANNUAL LEAVE   |20.2                   |2              |2
1002        |04/09/2017     |7.3    |9      |4      |2017   |Monday         |0              |ANNUAL LEAVE   |0      |ANNUAL LEAVE   |20.2                   |2              |2
1002        |09/08/2017     |2      |8      |9      |2017   |Wednesday      |1              |Admin          |A1     |Non-Billable   |20.2                   |2              |2
1002        |24/08/2017     |1.3    |8      |24     |2017   |Thursday       |4              |Client3        |A4     |Client         |20.2                   |2              |2
1002        |28/08/2017     |2.3    |8      |28     |2017   |Monday         |5              |Client4        |A5     |Client         |20.2                   |2              |2

I have attempted (and failed) doing this numerous ways. I think using sub queries is required but I can't seem to get the format correct.

Another issue I am finding hard to get my head around to produce the format I require is the count function, as I know this requires a group by clause but I want to keep all rows in my table for now.

I think I am overthinking the entire thing, so any help would be appreciated. Thanks in advance

Upvotes: 3

Views: 2815

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You can use window functions:

select t.*, sum(hours) over (partition by sequence_id) as sum_hours,
       max(codes_seqnum) over (partition by sequence_id) as num_codes,
       max(clients_seqnum) over (partition by sequence_id) as num_clients
from (select t.*,
             dense_rank() over (partition by sequence_id order by client) as codes_seqnum,
             dense_rank() over (partition by sequence_id, job_type order by (case when job_type = 'Client' then client end)) as client_seqnum
      from t
     ) t;

COUNT(DISTINCT) is actually tricky with window functions in SQL Server. The above basically works, but it won't return 0 if there are no clients. It might be better to just JOIN in the results:

select t.*, tt.sum_hours, tt.num_codes, tt.num_clients
from t join
     (select sequence_id, sum(hours) as sum_hours,
             count(distinct client) as num_codes,
             count(distinct case when job_type = 'Client' then client end) as num_clients
      from t
      group by sequence_id
     ) tt
     on tt.sequence_id = t.sequence_id;

Upvotes: 2

Related Questions