Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | The Grouping Puzzle – A Single SELECT | SQL Interview Question

In this puzzle you have to have find the sum of sales for each employee. The catch here is that for inactive employees(IsActive=0) group the sum under Inactive name and rest under their respective names. Another catch is that you need to do this in a single select.Please check out the sample input and the expected output.

Sample Input

EmpName SaleDate SaleAmount IsActive
Pawan 2018-08-07 10:18:28.663 2500.00 1
Pawan 2018-08-07 10:18:28.663 3000.00 1
Avtaar 2018-08-07 10:18:28.663 800.00 1
Avtaar 2018-08-07 10:18:28.663 1000.00 1
Kishan 2018-08-07 10:18:28.663 2800.00 1
Kishan 2018-08-07 10:18:28.663 3000.00 1
Nimit 2018-08-07 10:18:28.663 500.00 1
Nimit 2018-08-07 10:18:28.663 800.00 1
K 2018-08-07 10:18:28.663 1000.00 0
L 2018-08-07 10:18:28.663 1000.00 0
M 2018-08-07 10:18:28.663 500.00 0
J 2018-08-07 10:18:28.663 2500.00 0

Expected Output

EmpName SaleAmount
Avtaar 1800.00
Inactive 5000.00
Kishan 5800.00
Nimit 1300.00
Pawan 5500.00

Script – DDL and INSERT Sample Data

--

CREATE TABLE dbo.tbl_sales
(  
    EmpName VARCHAR(256),
    SaleDate DATETIME,
    SaleAmount DECIMAL(18,2),
    IsActive BIT
)
GO

INSERT INTO tbl_sales VALUES 
('Pawan',GETDATE(),2500.00,1),
('Pawan',GETDATE(),3000.00,1),
('Avtaar',GETDATE(),800.00,1),
('Avtaar',GETDATE(),1000.00,1),
('Kishan',GETDATE(),2800.00,1),
('Kishan',GETDATE(),3000.00,1),
('Nimit',GETDATE(),500.00,1),
('Nimit',GETDATE(),800.00,1),
('K',GETDATE(),1000.00,0),
('L',GETDATE(),1000.00,0),
('M',GETDATE(),500.00,0),
('J',GETDATE(),2500.00,0)
GO

SELECT * FROM tbl_sales
GO

--

–SOLUTION 1

--

SELECT DISTINCT IIF(IsActive = 0, 'Inactive',EmpName) EmpName , 
				SUM(SaleAmount) OVER (PARTITION BY  IIF(IsActive = 0, 'Inactive',EmpName))
				[SaleAmount]
FROM tbl_sales
GO

--

OUTPUT – 1

--

EmpName         SaleAmount
--------------- ---------------
Avtaar          1800.00
Inactive        5000.00
Kishan          5800.00
Nimit           1300.00
Pawan           5500.00

(5 rows affected)

--

–SOLUTION 2

--

SELECT IIF(IsActive = 0, 'Inactive',EmpName) EmpName 
	  ,SUM(SaleAmount) [SaleAmount]
FROM tbl_sales
GROUP BY IIF(IsActive = 0, 'Inactive',EmpName)
GO
--

OUTPUT – 2

--

EmpName         SaleAmount
--------------- ---------------
Avtaar          1800.00
Inactive        5000.00
Kishan          5800.00
Nimit           1300.00
Pawan           5500.00

(5 rows affected)

--

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Performance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com