INTERNET APPLICATION DEVELOPMENT
MID MARKET ERP DEVELOPMENT
by Emma Tsai
As a business analyst, I not only work with clients to get the requirements, but also perform functional testing for software applications. Sometimes, I need to work with SQL server to do database query and compare the results while testing. Recently, I found using Windows functions to perform data analysis computations in SQL server is pretty handy.
Basically, Windows aggregate functions are the same as the group aggregate functions (for example, SUM, AVG, MIN, and MAX), except that Windows aggregate functions are applied to rows defined by the “OVER” clause. Also, one of the benefits of using Window functions is that unlike group queries, Windows queries do not hide the details, which returns a row for every underlying query’s row.
For example, you can use Windows functions to query against a sales order view, and it returns the grand total of all values and the customer total.
Reference: Exam 70-461 Querying Microsoft SQL Server 2012 by Itzik Ben-Gan, Dejan Sarka, and Ron Talmage
If you have any questions about our testing programs, please contact me.
Good to know!