Tags

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


SQL Puzzle | The Quantity SUM Sales Puzzle

In this puzzle In the puzzle we have to change the SUM of sales amount column for type = ‘j’. First get the quantity for column for type = ‘j’ and then get sum of all sales for the quantity for type j. Please check the sample input and expected output.

Sample Input

SumQuantity Table

Quantity product desction type saleamount
315 xyz product1 NULL 100
315 mnd product2 NULL 200
315 dnd product3 NULL 563
14 dioa product4 NULL 456
315 xxx product5 J 144
1 ddd product6 NULL 226
315 dqi product7 NULL 144

Expected Output

Quantity product desction type saleamount
315 xyz product1 NULL 100
315 mnd product2 NULL 200
315 dnd product3 NULL 563
14 dioa product4 NULL 456
315 xxx product5 J 1151
1 ddd product6 NULL 226
315 dqi product7 NULL 144

Script

Use below script to create table and insert sample data into it.

--

USE [master]
GO
/****** Object:  Table [dbo].[SumQuantity]    Script Date: 12/7/2017 5:29:03 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SumQuantity](
	[Quantity] [int] NULL,
	[product] [varchar](5) NULL,
	[desction] [varchar](15) NULL,
	[type] [varchar](1) NULL,
	[saleamount] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[SumQuantity] ([Quantity], [product], [desction], [type], [saleamount]) VALUES (315, N'xyz', N'product1', NULL, 100)
GO
INSERT [dbo].[SumQuantity] ([Quantity], [product], [desction], [type], [saleamount]) VALUES (315, N'mnd', N'product2', NULL, 200)
GO
INSERT [dbo].[SumQuantity] ([Quantity], [product], [desction], [type], [saleamount]) VALUES (315, N'dnd', N'product3', NULL, 563)
GO
INSERT [dbo].[SumQuantity] ([Quantity], [product], [desction], [type], [saleamount]) VALUES (14, N'dioa', N'product4', NULL, 456)
GO
INSERT [dbo].[SumQuantity] ([Quantity], [product], [desction], [type], [saleamount]) VALUES (315, N'xxx', N'product5', N'J', 144)
GO
INSERT [dbo].[SumQuantity] ([Quantity], [product], [desction], [type], [saleamount]) VALUES (1, N'ddd', N'product6', NULL, 226)
GO
INSERT [dbo].[SumQuantity] ([Quantity], [product], [desction], [type], [saleamount]) VALUES (315, N'dqi', N'product7', NULL, 144)
GO

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution – 1

--


SELECT r.Quantity,r.product,r.desction,r.type,CASE WHEN r.type = 'j' THEN g.saleamount1 ELSE r.saleamount END saleamount FROM 
(
	SELECT * , CASE WHEN [type] = 'J' THEN Quantity END st
	FROM SumQuantity	
)r
CROSS APPLY
(
	SELECT SUM(saleamount) saleamount1
	FROM SumQuantity s 
	WHERE s.Quantity = r.st
)g

--

Output

--

/*------------------------

SELECT r.Quantity,r.product,r.desction,r.type,CASE WHEN r.type = 'j' THEN g.saleamount1 ELSE r.saleamount END saleamount FROM 
(
	SELECT * , CASE WHEN [type] = 'J' THEN Quantity END st
	FROM SumQuantity	
)r
CROSS APPLY
(
	SELECT SUM(saleamount) saleamount1
	FROM SumQuantity s 
	WHERE s.Quantity = r.st
)g
------------------------*/
Quantity    product desction        type saleamount
----------- ------- --------------- ---- -----------
315         xyz     product1        NULL 100
315         mnd     product2        NULL 200
315         dnd     product3        NULL 563
14          dioa    product4        NULL 456
315         xxx     product5        J    1151
1           ddd     product6        NULL 226
315         dqi     product7        NULL 144

(7 row(s) affected)


--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com