forum

Home / DeveloperSection / Forums / How to get Cumulative frequency in SQL Server

Ask Question

How to get Cumulative frequency in SQL Server

Anonymous User613910-Sep-2012
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!
mssql servermssql server 
Updated on 11-Sep-2012
Anonymous User

Anonymous User

Follow
I am a content writter !

Can you answer this question?

Answer

2 Answers

Anonymous User

Anonymous User

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

AVADHESH PATEL

10-Sep-2012
--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

Liked By