Aggregates without GroupBy SQL Server

When you use aggregate functions along with non-aggregate columns, all non-aggregate columns should be part of GROUP BY Clause. However with the new version of aggregate functions with OVER(), it can be used without the usage of GROUP BY clause.

Suppose you want to return the total number of rows along with a column, you can write count(*) as shown below

select name,count(*) over () as total_rows from sysobjects



The result shows the value for name column along with total number of rows in total_rows column. Suppose you want to return total number of object types along with a column, you can write count(*) as shown below

select name,xtype,count(*) over () as total_rows,count(*) over (partition by xtype order by xtype) as total_types from sysobjects



The results shows values for column name , xtype and total number of xtypes for each xtype value. This count differs for each xtype.

This way you can make use of COUNT function without using GROUP BY Clause. From version 2012, you can also make use of SUM function for calculating running total using this technique which you can find at http://www.sqlservercurry.com/2012/08/sql-server-2012-running-total-with-sum.html

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan