Query to return percentage based on a column by day.

Question

Hi All,

I am using SQL Server 2019 and trying to return percentages by day based on the Type column.

So basically looking to split out by percentage the type column by day.

So for example on 1/2/2020 I have 347 and 335 answered respectively for Cable and Wireless. So I simply do 347/(347+335) to get the percentage for Cable and 335/(347+335) to get the percentage for Wireless. As you can see for hundreds of entries this is a tedious task and hoping there is a way to simply this with a sql server query.

I have added the steps I did to create a temp table and insert some sample data, however that is as far as I am able to go.

CREATE TABLE [dbo].[temp](
    [Date] [date] NULL,
    [Answered] [float] NULL,
    [Type] [nvarchar](255) NULL
);



INSERT INTO temp VALUES('2020-01-02', '347', 'Cable');
INSERT INTO temp VALUES('2020-01-02', '335', 'Wireless');
INSERT INTO temp VALUES('2020-01-03', '286', 'Wireless');
INSERT INTO temp VALUES('2020-01-03', '306', 'Cable');
INSERT INTO temp VALUES('2020-01-04', '195', 'Cable');
INSERT INTO temp VALUES('2020-01-04', '229', 'Wireless');
INSERT INTO temp VALUES('2020-01-05', '217', 'Wireless');
INSERT INTO temp VALUES('2020-01-05', '170', 'Cable');
INSERT INTO temp VALUES('2020-01-06', '253', 'Wireless');
INSERT INTO temp VALUES('2020-01-06', '304', 'Cable');
INSERT INTO temp VALUES('2020-01-07', '297', 'Cable');
INSERT INTO temp VALUES('2020-01-07', '293', 'Wireless');
INSERT INTO temp VALUES('2020-01-08', '248', 'Wireless');
INSERT INTO temp VALUES('2020-01-08', '292', 'Cable');
INSERT INTO temp VALUES('2020-01-09', '267', 'Cable');
INSERT INTO temp VALUES('2020-01-09', '241', 'Wireless');

What I will like to see as a result is

Date    Answered    Type    pct%
1/2/2020     347                  Cable 0.508797654
1/2/2020     335                 Wireless   0.491202346
1/3/2020     286                 Wireless   0.483108108
1/3/2020     306                  Cable 0.516891892
1/4/2020     195                  Cable 0.45990566
1/4/2020     229                Wireless    0.54009434
1/5/2020     217                Wireless    0.560723514
1/5/2020     170                  Cable 0.439276486
1/6/2020     253               Wireless 0.454219031
1/6/2020     304                  Cable 0.545780969
1/7/2020     297                  Cable 0.503389831
1/7/2020     293                Wireless    0.496610169
1/8/2020     248                Wireless    0.459259259
1/8/2020     292                  Cable 0.540740741
1/9/2020     267                   Cable    0.525590551
1/9/2020     241               Wireless 0.474409449

Any help will be greatly appreciated.

Thanks

Answer 1

Select Date, Answered, Type, Answered/Sum(Answered) Over(Partition By Date) As [pct%]
From temp
Order By Date, Type;

Tom

Answer 2

Hi @Sylvester McLeod ,

Welcome to Microsoft Q&A!

Glad that you already got your answer.

Based on Tom's answer, you could use CAST function to accurate to exact decimal places like below:

Select Date, Answered, Type, cast(Answered/Sum(Answered) Over(Partition By Date) as decimal(9,9)) As [pct%]  
From temp  
Order By Date, Type;  

Output:

Date	Answered	Type	pct%  
2020-01-02	347	Cable	0.508797654  
2020-01-02	335	Wireless	0.491202346  
2020-01-03	306	Cable	0.516891892  
2020-01-03	286	Wireless	0.483108108  
2020-01-04	195	Cable	0.459905660  
2020-01-04	229	Wireless	0.540094340  
2020-01-05	170	Cable	0.439276486  
2020-01-05	217	Wireless	0.560723514  
2020-01-06	304	Cable	0.545780969  
2020-01-06	253	Wireless	0.454219031  
2020-01-07	297	Cable	0.503389831  
2020-01-07	293	Wireless	0.496610169  
2020-01-08	292	Cable	0.540740741  
2020-01-08	248	Wireless	0.459259259  
2020-01-09	267	Cable	0.525590551  
2020-01-09	241	Wireless	0.474409449  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi

SELECT day(CONVERT(varchar, [DateDay], 23)) as myday from temp

Best Regards.
Please remember to mark the replies as answers if they help.