TSQL Max and Over Clause

You can use the MIN, MAX, AVG and COUNT functions with the OVER clause to provide aggregated values on multiple rows.

For instance:

SELECT Name

 , MIN(Rate) OVER (PARTITION BY edh.DepartmentID) AS MinSalary

 , MAX(Rate) OVER (PARTITION BY edh.DepartmentID) AS MaxSalary

 , AVG(Rate) OVER (PARTITION BY edh.DepartmentID) AS AvgSalary

 ,COUNT(edh.BusinessEntityID) OVER (PARTITION BY edh.DepartmentID) AS EmployeesPerDept

FROM EmployeePayHistory AS eph

      JOIN EmployeeDepartmentHistory AS edh

            ON eph.BusinessEntityID = edh.BusinessEntityID

      JOIN Department AS d

            ON d.DepartmentID = edh.DepartmentID


http://msdn.microsoft.com/en-us/library/ms187751.aspx

Comments

Post a Comment

Popular posts from this blog

Cross Apply and Outer Apply

The APPLY operator allows you to return values from an outer table much like JOIN, however I find it's easier to read, and allows joining two tables that I would've had to do some fancy foot work on to join. And since I'm always looking for the simpliest solution, this one fit the bill on my lastest project. There are two forms of APPLY: CROSS APPLY and OUTER APPLY.  CROSS APPLY returns only rows from the outer table that produce a result. It works similiar to INNER JOIN in that it won't return values that are null. The OUTER APPLY returns either values, or nulls if no values exist. The value of using the APPLY operator is that it can yield better performance. Check out http://explainextended.com/2009/07/16/inner-join-vs-cross-apply/  to read more.     Information on how the Apply operator works   Here's a link to Technet's article on the APPLY operator: https://technet.microsoft.com/en-us/library/ms175156(SQL.90).aspx   Another artic...
Read more

SSRS Issues attaching Reports to Email subscriptions

I was attempting to set up an email subscription on our production server the other day but kept getting errors when the subscription ran. The development server subscriptions ran fine, the configuration settings for both servers appeared identical. The weird thing was that I could send a subscription through email as long as I didn't attach the report. After much searching, and pulling out my hair, I finally found this KB article: A call to the "AuthzInitializeContextFromSid" API function fails during the delivery of an e-mail subscription in SQL Server Reporting Services My hint was the error in the error log: ERROR: Throwing Microsoft.ReportingServices. Diagnostics. Utilities. ServerConfigurationErrorException: The Report Server has encountered a configuration error; more details in the log files, AuthzInitializeContextFromSid: Win32 error: 5; possible reason - service account doesn't have rights to check domain user SIDs.; KB article 842423 walks yo...
Read more