You must Sign In to post a response.
• # How to display the summation of table data

Are you looking for a way to display the summation of table data ? then read this thread to know how to display it

I have below table with data

City Purchase channel Amount

Mumbai A AA 1000
Mumbai B BB 1000
Mumbai A AA 2000
Mumbai C CC 1000
Mumbai A AA 1000
Mumbai D AA 1000
Mumbai D AA 1000
Mumbai A NULL 1000
Pune A AA 1000
Pune C AA 2000
Pune A AA 1000
Pune D BB 1000
Pune B CC 1000
Nashik B CC 1000
Pune A NULL 1000
nashik A NULL 4000
nashik C cc 2000
nashik B BB 3000
nashik D AA 4000
nashik D BB 1500
Pune B CC 1000
Nashik A AA 1000

I want do display the data from above table like below

City P(A+B) R(C+D+E) Net(P-R)
Mumbai
Pune
nashik

P(A+B)=sum of all A and B for city

R(C+D+E)=sum of all C,D and E for city

Net(P-R)=[P-R]

what is the query to display the data?

Thanks
• Hi Sandeep could you please explain/Elaborate the question somewhat clear,since i cant understand your question.
Rajesh B
To Live More,Learn More

• Please explain us the logic as to what you want to achieve. It is not clear what kind of summation of data you want to do. Do you want to group by some city and then computation (SUM) based on each city.?
Miss. Jain
Microsoft Certified Technology Specialist in .Net

• Hi Priya mam,

I want to group by some city and then computation (SUM)of amount based on each city.

eg
It should display like below
City P(A+B) R(C+D+E) Net(P-R)
Mumbai 1000 500 500
Pune 2000 1000 1000
nashik 3000 1500 1500

Thanks

• Hi,

Use the below sql script

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[temp](
[City] [varchar](10) NULL,
[Purchase] [varchar](10) NULL,
[channel] [varchar](10) NULL,
[Amount] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[temp] ([City], [Purchase], [channel], [Amount]) VALUES (N'Mumbai', N'A', N'AA', 1000)
GO
INSERT [dbo].[temp] ([City], [Purchase], [channel], [Amount]) VALUES (N'Mumbai', N'B', N'BB', 1000)
GO
INSERT [dbo].[temp] ([City], [Purchase], [channel], [Amount]) VALUES (N'Mumbai', N'A', N'AA', 2000)
GO
INSERT [dbo].[temp] ([City], [Purchase], [channel], [Amount]) VALUES (N'Mumbai', N'C', N'CC', 1000)
GO
INSERT [dbo].[temp] ([City], [Purchase], [channel], [Amount]) VALUES (N'Mumbai', N'A', N'AA', 1000)
GO
INSERT [dbo].[temp] ([City], [Purchase], [channel], [Amount]) VALUES (N'Mumbai', N'D', N'AA', 1000)
GO
INSERT [dbo].[temp] ([City], [Purchase], [channel], [Amount]) VALUES (N'Mumbai', N'D', N'AA', 1000)
GO
INSERT [dbo].[temp] ([City], [Purchase], [channel], [Amount]) VALUES (N'Mumbai', N'A', NULL, 1000)
GO
INSERT [dbo].[temp] ([City], [Purchase], [channel], [Amount]) VALUES (N'Pune', N'A', N'AA', 1000)
GO
INSERT [dbo].[temp] ([City], [Purchase], [channel], [Amount]) VALUES (N'Pune', N'C', N'AA', 2000)
GO
INSERT [dbo].[temp] ([City], [Purchase], [channel], [Amount]) VALUES (N'Pune', N'A', N'AA', 1000)
GO
INSERT [dbo].[temp] ([City], [Purchase], [channel], [Amount]) VALUES (N'Pune', N'D', N'BB', 1000)
GO
INSERT [dbo].[temp] ([City], [Purchase], [channel], [Amount]) VALUES (N'Pune', N'B', N'CC', 1000)
GO
INSERT [dbo].[temp] ([City], [Purchase], [channel], [Amount]) VALUES (N'Nashik', N'B', N'CC', 1000)
GO
INSERT [dbo].[temp] ([City], [Purchase], [channel], [Amount]) VALUES (N'Pune', N'A', NULL, 1000)
GO
INSERT [dbo].[temp] ([City], [Purchase], [channel], [Amount]) VALUES (N'nashik', N'A', NULL, 4000)
GO
INSERT [dbo].[temp] ([City], [Purchase], [channel], [Amount]) VALUES (N'nashik', N'C', N'cc', 2000)
GO
INSERT [dbo].[temp] ([City], [Purchase], [channel], [Amount]) VALUES (N'nashik', N'B', N'BB', 3000)
GO
INSERT [dbo].[temp] ([City], [Purchase], [channel], [Amount]) VALUES (N'nashik', N'D', N'AA', 4000)
GO
INSERT [dbo].[temp] ([City], [Purchase], [channel], [Amount]) VALUES (N'nashik', N'D', N'BB', 1500)
GO
INSERT [dbo].[temp] ([City], [Purchase], [channel], [Amount]) VALUES (N'Pune', N'B', N'CC', 1000)
GO
INSERT [dbo].[temp] ([City], [Purchase], [channel], [Amount]) VALUES (N'Nashik', N'A', N'AA', 1000)
GO

SELECT DISTINCT city AS City,
P.amount AS [P],
R.amount AS [R],
( P.amount - R.amount ) AS [Net]
FROM temp t
CROSS APPLY (SELECT SUM(amount) AS Amount
FROM temp
WHERE purchase IN ( 'A', 'B' )
AND city = t.city)P
CROSS APPLY (SELECT SUM(amount) AS Amount
FROM temp
WHERE purchase IN ( 'C', 'D', 'E' )
AND city = t.city)R

DROP TABLE temp

• Sign In to post your comments