CODEWITHSUNDEEP
sql-servert-sql
Chris
Chris

Reputation: 343

How can I sum certain columns based on individual dates and unique IDs

The attached image represents my SQL table of data. I am trying to create two new columns that have the sum of the total minutes and the count of services per distinct case number for each day. For example, if a case number appears three times that mean there were three services provided but I want the total minutes and services totaled separately for each day and for each case number. I've tried the query below and it gives me the count of services but not how many per day nor the total minutes. 

SELECT *,
COUNT(CASE_NUM) OVER (PARTITION BY CASE_NUM) as AGGRE_SERVICES,
SUM(TOTAL_MIN) OVER (PARTITION BY DATE) as AGGRE_MINS
FROM #SQLTABLE

Upvotes: 0

Views: 41

Answers (1)

Dance-Henry
Dance-Henry

Reputation: 953

Try this code below.

SELECT *,
    COUNT(CASE_NUM) OVER (PARTITION BY CASE_NUM, DATE) as AGGRE_SERVICES,
    SUM(TOTAL_MIN) OVER (PARTITION BY CASE_NUM, DATE) as AGGRE_MINS
    FROM #SQLTABLE

Upvotes: 1

Related Questions