Divide Total Rate by Number of Records

Question

Post reply

Divide Total Rate by Number of Records

helal.mobasher 13209

SSCommitted

Points: 1526
More actions
June 22, 2015 at 1:25 pm

#309389

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

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

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

g.britton SSChampion Points: 13863 More actions · Answer 1

select Providerid, Providername, Clientid

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

from table1

Gerald Britton, Pluralsight courses