Home > DeveloperSection > Forums > How to get Cumulative frequency in SQL Server
Tanuj Kumar

Total Post:134

Points:940
Posted on    September-10-2012 1:37 AM

 MSSQL Server MSSQL Server 
Ratings:


 2 Reply(s)
 3540  View(s)
Rate this:
Hello Everyone,
I've a small requirement; Actually I've a table which has three column name such as ID, Product_Name and Product_Quantity, these columns contains value like:
ID                         Product_Name                     Product_Quantity
1                              Parle G                                     10
2                              Hair Oil                                    20
3                              Soap                                         30
4                              Shampoo                                  49

In this table I want to add another column name like Product_CumulativeValue which contains cumulative value of Product_Quantity column, so can you tell me how to find out the cumulative value of Product_Quantity column.

Please resolve my problem as soon as possible.
Thanks in advance!


AVADHESH PATEL

Total Post:604

Points:4228
Posted on    September-10-2012 10:04 PM

--Calculate Cumulative Frequency in SQL Server

--Create Database
CREATE DATABASE [AldSalesPvtLtd]
GO

--Use Database
USE [AldSalesPvtLtd]
GO

--Create Table
CREATE TABLE [dbo].[Product]
(
[ID] INT PRIMARY KEY,
[Product_Name] VARCHAR(50) NOT NULL,
[Product_Quantity] INT NOT NULL
)
GO

--Insert Record into Student Table
INSERT INTO [dbo].[Product]([ID],[Product_Name],[Product_Quantity])
SELECT 1,'Parle G',10
UNION ALL
SELECT 2,'Hair Oil',20
UNION ALL 
SELECT 3,'Soap',30
UNION ALL
SELECT 4,'Shampoo',40
GO

-- Display Record of Student Table
SELECT [ID],[Product_Name],[Product_Quantity] FROM [dbo].[Product]
GO

-- Create Temporary Table
CREATE TABLE #Product
(
[ID] INT PRIMARY KEY,
[Product_Name] VARCHAR(50) NOT NULL,
[Product_Quantity] INT NOT NULL,
[Product_CumulativeValue] INT NOT NULL --[Product_CumulativeValue] column Stored Calculated Cumulative Frequency result
)
GO

-- Declare Variables and set values
DECLARE @ID INT,
@Product_Name VARCHAR(50),
@Product_Quantity INT,
        @Product_CumulativeValue INT
SET @Product_CumulativeValue = 0

-- Create Corsor
DECLARE CF_Cursor CURSOR
FOR
SELECT [ID],[Product_Name],[Product_Quantity]
FROM [dbo].[Product]

--Open Corsor
OPEN CF_Cursor

-- Select Record from cursor and stored into variables
FETCH NEXT FROM CF_Cursor INTO @ID, @Product_Name,@Product_Quantity

--@@FETCH_STATUS Returns the status of the last cursor FETCH statement issued against any cursor currently opened by the connection.
WHILE @@FETCH_STATUS = 0 -- 0 indicate FETCH statement was successful.
 BEGIN
  SET @Product_CumulativeValue = @Product_CumulativeValue + @Product_Quantity
  INSERT #Product VALUES (@ID,@Product_Name,@Product_Quantity,@Product_CumulativeValue)
  FETCH NEXT FROM CF_Cursor INTO @ID, @Product_Name,@Product_Quantity
 END

-- Close Cursor
CLOSE CF_Cursor
-- Deallocate Cursor
DEALLOCATE CF_Cursor

-- Select * from Temporary Table #Student
SELECT * FROM #Product
GO

Tanuj Kumar

Total Post:134

Points:940
Posted on    September-11-2012 2:33 AM

Hi AVADHESH PATEL,
Hurrah!!! This code resolve my problem. Thank you very much Avadhesh. Keep it up.

Modified On Mar-15-2016 05:37:42 AM

Don't want to miss updates? Please click the below button!

Follow MindStick