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

Trying to Compare Between Two Table

Introduction

We are trying to compare between two tables.  There is lot of method to compare but we are trying to use a simple one. Hope it will be interesting.

How we Compare ?

Step – 1  [ Create the Base Table  ]

CREATE TABLE TEST
(
   ID           INT,
   NAME   CHAR(1));

GO

CREATE TABLE TEST1
(
   ID           INT,
   NAME   CHAR(1));
GO

Step – 2 [ Insert some records in it ]

INSERT INTO TEST VALUES(1, 'A'),(2,'B');

INSERT INTO TEST1 VALUES(1, 'A'),(2,'B');

Step – 3 [ Now compare – But both table have same records ]

SELECT MIN(a.TableName) AS TableName, a.ID, a.NAME
FROM (
SELECT 'TEST' AS TableName, ID, NAME FROM TEST
UNION ALL
SELECT 'TEST1' AS TableName,  ID, NAME FROM TEST1
)a
GROUP BY a.ID, a.NAME
HAVING COUNT(*) = 1;

Output:
Nothing.


Step – 4 [ Now Change some records in both Table ]

INSERT INTO TEST1 VALUES(1, 'A'),(2,'B');
INSERT INTO TEST1 VALUES(4, 'D');

Step – 5 [ Now Compare it Again ]

SELECT MIN(a.TableName) AS TableName, a.ID, a.NAME
FROM (
SELECT 'TEST' AS TableName, ID, NAME FROM TEST
UNION ALL
SELECT 'TEST1' AS TableName,  ID, NAME FROM TEST1
)a
GROUP BY a.ID, a.NAME
HAVING COUNT(*) = 1;

Output:

TableName ID NAME
TEST1 4  D
TEST 3 X




Hope you like it.




Posted By: MR. JOYDEEP DAS

Default Value of SELECT When NO Records Found

Introduction

We are using SELECT statement to retrieve data from table. But here we are trying to construct such kind of SELECT, if no records found it returns a Default Value.
Hope it would be informative.

How We DO That  ?

Step – 1 [ Create Base Table and Insert Records ]

CREATE TABLE tbl_Employee
  (
     EmpID          INT NOT NULL,
     EmpName     VARCHAR(50) NOT NULL
  );


GO

INSERT INTO tbl_Employee
VALUES(1,  'Joydeep Das'),
                (2,  'Deepaasree Das'),
        (3,  'Santi Nath');


Step – 2  [ Now Try with EmpID that we have in the Table ]

SELECT COALESCE(A.EmpName, B.EmpName) AS EmpName
FROM (
SELECT EmpName AS EmpName FROM tbl_Employee WHERE EmpID = 1) A
       RIGHT OUTER JOIN (SELECT 'Not Found' AS EmpName) B ON 1 = 1;

Output:

EmpName
Joydeep Das

Step – 3 [ Now Try with EmpID that we Don’t have in Table ]

SELECT COALESCE(A.EmpName, B.EmpName) AS EmpName
FROM (
SELECT EmpName AS EmpName FROM tbl_Employee WHERE EmpID = 10) A
       RIGHT OUTER JOIN (SELECT 'Not Found' AS EmpName) B ON 1 = 1;

Output:

EmpName
Not Found




Hope you understand it.




Posted By: MR. JOYDEEP DAS

TSQL trick only with FOR XML Support

Introduction

In this article we are going to demonstrate a TSQL trick only with FOR XML Support.

Case Study

We have three Table Objects

tbl_CUSTOMER

CUSTID	CUSTNAME
1	Joydeep Das
2	Chandan Bannerjee
3	Soumen Bhowmik

tbl_ITEMDTLS

ITEMCD	ITEMNAME
100	Tooth Paste
101	Tooth Brusg
102	Saving Lotion
103	Saving Brush

Now the customer purchase Items

tbl_SALEDTLS

SALENO	SRLNO	CUSTID	ITEMCD
201	1	1	100
201	2	1	101
201	3	1	102
201	4	1	103
202	1	2	100
202	2	2	101
203	1	3	100

We want a report like this Format

CUSTID	CUSTNAME	ITEM DETAILS
1	Joydeep Das	Saving Brush, Saving Lotion, Tooth Brusg, Tooth Paste
2	Chandan Bannerjee	Tooth Brusg, Tooth Paste
3	Soumen Bhowmik	Tooth Paste
1	Joydeep Das	Saving Brush, Saving Lotion, Tooth Brusg, Tooth Paste

How to solve it

-- Table Object Customer

IF OBJECT_ID(N'dbo.tbl_CUSTOMER', N'U')IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[tbl_CUSTOMER];

   END

GO

CREATE TABLE [dbo].[tbl_CUSTOMER]

      (

         CUSTID    INT         NOT NULL IDENTITY PRIMARY KEY,

         CUSTNAME  VARCHAR(50) NOT NULL

      );

GO

-- Insert Records

INSERT INTO [dbo].[tbl_CUSTOMER]

      (CUSTNAME)

VALUES('Joydeep Das'),

      ('Chandan Bannerjee'),

      ('Soumen Bhowmik');                 

-- Table Object Item Details

IF OBJECT_ID(N'dbo.tbl_ITEMDTL', N'U')IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[tbl_ITEMDTL];

   END

GO

CREATE TABLE [dbo].[tbl_ITEMDTL]

   (

     ITEMCD       INT         NOT NULL IDENTITY(100,1) PRIMARY KEY,

     ITEMNAME     VARCHAR(50) NOT NULL

   )

GO

-- Insert Records

INSERT INTO [dbo].[tbl_ITEMDTL] 

    (ITEMNAME)

VALUES('Tooth Paste'),

      ('Tooth Brusg'),

      ('Saving Lotion'),

      ('Saving Brush');

     

-- Table Object Sales Dtls

IF OBJECT_ID(N'dbo.tbl_SALEDTLS', N'U')IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[tbl_SALEDTLS];

   END

GO           

CREATE TABLE [dbo].[tbl_SALEDTLS]

   (

     SALENO   INT   NOT NULL,

     SRLNO    INT   NOT NULL,

     CUSTID   INT   NOT NULL,

     ITEMCD   INT   NOT NULL,

     CONSTRAINT PK_tbl_SALEDTLS PRIMARY KEY

        (

           SALENO ASC,

           SRLNO  ASC

        )

   )           

GO

-- Insert Records

INSERT INTO [dbo].[tbl_SALEDTLS]

      (SALENO, SRLNO, CUSTID, ITEMCD)

VALUES(201, 1, 1, 100),

      (201, 2, 1, 101),

      (201, 3, 1, 102),

      (201, 4, 1, 103),

      (202, 1, 2, 100),

      (202, 2, 2, 101),

      (203, 1, 3, 100);  

     

GO

SELECT * FROM  [dbo].[tbl_CUSTOMER];

SELECT * FROM  [dbo].[tbl_ITEMDTL];

SELECT * FROM  [dbo].[tbl_SALEDTLS]; 

-- Query

SELECT a.CUSTID, a.CUSTNAME,

       STUFF((SELECT ', '+ y.ITEMNAME

              FROM   [dbo].[tbl_SALEDTLS] AS x

                     INNER JOIN [dbo].[tbl_ITEMDTL] AS y

              ON x.ITEMCD = y.ITEMCD

              WHERE  x.CUSTID = a.CUSTID

              ORDER BY ',' + y.ITEMNAME

              FOR XML PATH('')),1,1,'') AS [ITEM DETAILS]

FROM   [dbo].[tbl_CUSTOMER] AS a; 

CUSTID   CUSTNAME                      ITEM DETAILS

1              Joydeep Das                    Saving Brush, Saving Lotion, Tooth Brusg, Tooth Paste

2              Chandan Bannerjee       Tooth Brusg, Tooth Paste

3              Soumen Bhowmik           Tooth Paste

Hope you like it.

Posted by: MR. JOYDEEP DAS