Divide Total Rate by Number of Records

  • my table includes detailed records with total Rate repeated in each record:

    CREATE TABLE Table1

    (

    Providerid varchar (6) NOT NULL,

    Providername char (30) NOT NULL,

    Clientid varchar (15) NOT NULL,

    Rate float

    )

    INSERT INTO Table1 (Providerid, Providername, Clientid, Rate)

    Values ('123' , 'Janesmith', '23041', 217);

    INSERT INTO Table1 (Providerid, Providername, Clientid, Rate)

    Values ('123' , 'Janesmith', '23042', 217);

    INSERT INTO Table1 (Providerid, Providername, Clientid, Rate)

    Values ('123' , 'Janesmith', '23043', 217);

    INSERT INTO Table1 (Providerid, Providername, Clientid, Rate)

    Values ('123' , 'Janesmith', '23044', 217);

    INSERT INTO Table1 (Providerid, Providername, Clientid, Rate)

    Values ('123' , 'Janesmith', '23045', 217);

    INSERT INTO Table1 (Providerid, Providername, Clientid, Rate)

    Values ('123' , 'Janesmith', '23046', 217);

    INSERT INTO Table1 (Providerid, Providername, Clientid, Rate)

    Values ('123' , 'Janesmith', '23047', 217)

    I need to divide the total rate (217) by total number of records (7) in this case so the results be like:

    CREATE TABLE Table1

    (

    Providerid varchar (6) NOT NULL,

    Providername char (30) NOT NULL,

    Clientid varchar (15) NOT NULL,

    Rate float

    )

    INSERT INTO Table1 (Providerid, Providername, Clientid, Rate)

    Values ('123' , 'Janesmith', '23041', 217);

    INSERT INTO Table1 (Providerid, Providername, Clientid, Rate)

    Values ('123' , 'Janesmith', '23042', 217);

    INSERT INTO Table1 (Providerid, Providername, Clientid, Rate)

    Values ('123' , 'Janesmith', '23043', 217);

    INSERT INTO Table1 (Providerid, Providername, Clientid, Rate)

    Values ('123' , 'Janesmith', '23044', 217);

    INSERT INTO Table1 (Providerid, Providername, Clientid, Rate)

    Values ('123' , 'Janesmith', '23045', 217);

    INSERT INTO Table1 (Providerid, Providername, Clientid, Rate)

    Values ('123' , 'Janesmith', '23046', 217);

    INSERT INTO Table1 (Providerid, Providername, Clientid, Rate)

    Values ('123' , 'Janesmith', '23047', 217)

    Thank You,

    Helal

  • select Providerid, Providername, Clientid

    , (Rate / count(*) over()) as [divided rate]

    from table1

    Gerald Britton, Pluralsight courses

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply