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