HOW TO GET CUMULATIVE FREQUENCY IN SQL SERVER

Tanuj Kumar

Total Post:134

Points:940
Posted by  Tanuj Kumar
 4471  View(s)
Ratings:
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!
  1. AVADHESH PATEL

    Post:604

    Points:4228
    Re: How to get Cumulative frequency in SQL Server

    --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

  1. Tanuj Kumar

    Post:134

    Points:940
    Re: How to get Cumulative frequency in SQL Server

    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

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!