CODEWITHSUNDEEP
sql-servercountsumpartition
Brian
Brian

Reputation: 11

MS SQL - sum then divide by count

I have written the following SQL query which provides the total number of days per employee. 

SELECT DISTINCT
  ([Entered_By]),
  SUM(ALL DATEDIFF(DAY, Time_End_UTC, Date_Entered_UTC)) OVER (PARTITION BY entered_by) AS Days

FROM [dbo].[Time_Entry]
WHERE date_start BETWEEN '06-01-2015' AND '06-30-2015'
AND DATEDIFF(DAY, Time_End_UTC, Date_Entered_UTC) > 2

I can also change "sum" to "count" (and remove the "all") and get a total number of records.

However I need one of the following:

  1. a column with sum and another with count (keep in mind that I can't write to this database) OR
  2. a column with the sum multiplied by the count

When I try to have both (option 1), I run into issues. And I can't figure out how to get the multiplication (option 2) to work.

Thanks for your help.

Upvotes: 1

Views: 4444

Answers (2)

Michael Petito
Michael Petito

Reputation: 13161

I feel like this would be much easier to achieve with a GROUP BY query instead of a windowed sum:

Select [Entered_By]
,sum(datediff(Day, Time_End_UTC, Date_Entered_UTC))
,count(*) --count of all records in the group, which you could also multiply by the sum if you prefer option 2

From [dbo].[Time_Entry]
where date_start between '06-01-2015' and '06-30-2015'
and datediff(DAY, Time_End_UTC, Date_Entered_UTC) >2
GROUP BY [Entered_By] --this takes care of the distinctness of [Entered_By] and partitioning you had in your original query

Upvotes: 2

Nathu
Nathu

Reputation: 262

For option 2, you can just

SELECT COUNT(ATTRIB) * SUM(ATTRIB) FROM TABLE;

Upvotes: 0

Related Questions