You must Sign In to post a response.
  • Category: SQL Server

    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
  • #750492
    Hi Sandeep could you please explain/Elaborate the question somewhat clear,since i cant understand your question.
    Rajesh B
    To Live More,Learn More

  • #750506
    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

  • #750647
    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

  • #750760
    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