T-SQL Tips – Aggregate Function without GROUP BY Clause

Introduction

Here in this article we are going to demonstrate simple tips of T-SQL.
We all know that if we use the Aggregate function in SELECT statement we must use the GROUP BY Clause.

But we can use the Aggregate function without GROUP BY clause. To understand it properly let’s take an example.

Example

I have a Table object.

n  Creating Table

CREATE TABLE [dbo].[tbl_ZoneWiseYearlySales]

  (

      ZoneID     INT,

SYear      INT,

     SAmt       DECIMAL(18,2)

  );

GO

n  Inserting Some records

INSERT INTO [dbo].[tbl_ZoneWiseYearlySales]

(ZoneID, SYear, SAmt)

VALUES(1, 2015, 10000),

       (1, 2016, 8000),

       (1, 2017, 7000),

       (2, 2015, 5000),

       (2, 2016, 3000),

       (3, 2017, 2000),

       (3, 2015, 1000),

       (3, 2016, 500),

       (3, 2017, 200);

GO

n  Display the Records

SELECT ZoneID, SYear, SAmt

FROM   [dbo].[tbl_ZoneWiseYearlySales];

GO

Output:

ZoneID	SYear	SAmt
1	2015	10000
1	2016	8000
1	2017	7000
2	2015	5000
2	2016	3000
3	2017	2000
3	2015	1000
3	2016	500
3	2017	200

Now we are trying to make the Zone wise TOTAL Sales amount.

SELECT ZoneID, SUM(SAmt) AS [Total Sales]

FROM   [dbo].[tbl_ZoneWiseYearlySales]

GROUP BY ZoneID;

GO

Output:

ZoneID	Total Sales
1	25000
2	8000
3	3700

What happens if we are not providing any GROUP BY Clause in the above SQL Statement.

SELECT ZoneID, SUM(SAmt) AS [Total Sales]

FROM   [dbo].[tbl_ZoneWiseYearlySales];

GO

Msg 8120, Level 16, State 1, Line 1

Column 'dbo.tbl_ZoneWiseYearlySales.ZoneID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Now What’s New

Try this one

SELECT DISTINCT ZoneID,    

       SUM(SAmt) OVER(PARTITION BY ZoneID) AS [Total Sales]

FROM   [dbo].[tbl_ZoneWiseYearlySales];

GO

Output:

ZoneID	Total Sales
1	25000
2	8000
3	3700

Now Analyze the Execution Plan

SELECT ZoneID, SUM(SAmt) AS [Total Sales]

FROM   [dbo].[tbl_ZoneWiseYearlySales]

GROUP BY ZoneID;

SELECT DISTINCT ZoneID,    

       SUM(SAmt) OVER(PARTITION BY ZoneID) AS [Total Sales]

FROM   [dbo].[tbl_ZoneWiseYearlySales];

GO

Posted By: MR. JOYDEEP DAS