Tricks to eliminate GROUP BY problems in SQL

Most of us having trouble with using GROUP BY clause in SQL. GROUP BY class is painful, because we cannot select the columns other than the columns used in GROUP BY. We have another concept to get rid of GROUP BY clause problems.

The OVER(PARTITION BY column_name) will work like a GROUP BY clause, and we can do the same with this clause without any limitation of GROUP BY.   Can we use any aggregate function without using GROUP BY? Yes. We can. Please execute the below SQL query sample to know about OVER(PARTITION BY …) class.

Sample:

IF OBJECT_ID('tempdb..#student_details') IS NOT NULL

      DROP TABLE #student_details

     

CREATE TABLE #student_details

(

       student_id UNIQUEIDENTIFIER

      ,student_ident INT IDENTITY(1,1) PRIMARY KEY

      ,student_name VARCHAR(15)

)

GO

DECLARE @duplicte UNIQUEIDENTIFIER

SET @duplicte = NEWID()

INSERT INTO #student_details (student_id,student_name) VALUES

(NEWID(),'SMITH')

,(@duplicte,'JONES')

,(NEWID(),'MARTIN')

,(NEWID(),'JAMES')

,(NEWID(),'ALLEN')

,(@duplicte,'JONES')

GO

-- To select duplicate records, we use below code using GROUP BY clause. We are restircted to not to select student_ident column.

SELECT

       student_id

      ,student_name

FROM

      #student_details

GROUP BY

       student_id

      ,student_name

HAVING

      COUNT(1) > 1

     

-- New Method. Here we can select the columns what ever we want (i.e. student_ident)

;WITH [duplicate] AS

(

SELECT

      'RN' = ROW_NUMBER() OVER (PARTITION BY student_id, student_name ORDER BY student_ident ASC)

      ,student_ident

      ,student_id

      ,student_name

FROM

      #student_details

)

SELECT * FROM [duplicate] WHERE RN > 1

-- Can we use the aggreagate functions with out using GOUP BY clause?. Yes. We can.

SELECT

       MIN(student_ident) OVER (PARTITION BY student_id, student_name)

      ,MAX(student_ident) OVER (PARTITION BY student_id, student_name)

      ,COUNT(1) OVER (PARTITION BY student_ident, student_name)

      ,AVG(student_ident) OVER (PARTITION BY student_id, student_name)

      ,student_id

      ,student_name

FROM

      #student_details