aggregate-functions

The SQL OVER() clause – when and why is it useful?

by

USE AdventureWorks2008R2; GO SELECT SalesOrderID, ProductID, OrderQty ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS ‘Total’ ,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS ‘Avg’ ,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS ‘Count’ ,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS ‘Min’ ,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS ‘Max’ FROM Sales.SalesOrderDetail WHERE SalesOrderID IN(43659,43664); I read about that clause and I don’t understand why I need it. … Read more

Function to Calculate Median in SQL Server

by

According to MSDN, Median is not available as an aggregate function in Transact-SQL. However, I would like to find out whether it is possible to create this functionality (using the Create Aggregate function, user defined function, or some other method). What would be the best way (if possible) to do this – allow for the … Read more

Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause [duplicate]

by

This question already has answers here: GROUP BY / aggregate function confusion in SQL (5 answers) Closed 2 years ago. I got an error – Column ‘Employee.EmpID’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. select loc.LocationID, emp.EmpID from Employee as emp … Read more

Apply multiple functions to multiple groupby columns

by

The docs show how to apply multiple functions on a groupby object at a time using a dict with the output column names as the keys: In [563]: grouped[‘D’].agg({‘result1’ : np.sum, …..: ‘result2’ : np.mean}) …..: Out[563]: result2 result1 A bar -0.579846 -1.739537 foo -0.280588 -1.402938 However, this only works on a Series groupby object. … Read more

must appear in the GROUP BY clause or be used in an aggregate function

by

I have a table that looks like this caller ‘makerar’ cname | wmname | avg ——–+————-+———————— canada | zoro | 2.0000000000000000 spain | luffy | 1.00000000000000000000 spain | usopp | 5.0000000000000000 And I want to select the maximum avg for each cname. SELECT cname, wmname, MAX(avg) FROM makerar GROUP BY cname; but I will get … Read more

SQL Server: Difference between PARTITION BY and GROUP BY

by

I’ve been using GROUP BY for all types of aggregate queries over the years. Recently, I’ve been reverse-engineering some code that uses PARTITION BY to perform aggregations. In reading through all the documentation I can find about PARTITION BY, it sounds a lot like GROUP BY, maybe with a little extra functionality added in? Are … Read more

SQL select only rows with max value on a column [duplicate]

by

Want to improve this post? Provide detailed answers to this question, including citations and an explanation of why your answer is correct. without enough detail may be edited or deleted. This question already has answers here: Retrieving the last record in each group – MySQL (32 answers) Closed 3 years ago. I have this table … Read more