How to query the percent of totals in a group by query

I have a query that pulls the total number of records grouped by the year and length of characters in the identification number. I want to add a column that calculates the percentage of this total against the total for each record year.

This is the query that I am using, and so far I'm able to pull the year, ID length, and the number of records; however, I'm unable to pull the percentage of the total, grouping by the year:

/* Assess length of McrCertID_112 field for 2020. Count the number and percent of records: */
SELECT
    DemoRateYear_101,
    length(McrCertId_112), 
    count(*) AS 'Num_of_Records',
    concat((count(*) / (SELECT count(*) FROM upl_db_prod.tblProviderDetails) * 100), '%') AS 'Percentage'
FROM upl_db_prod.tblProviderDetails
GROUP BY length(McrCertId_112), DemoRateYear_101
ORDER BY DemoRateYear_101, length(McrCertId_112);

This is what I want the final table to look like:

DemoRateYear_101   length(McrCertId_112)   Num_of_Records    Percentage
2017               4                       10                47.6190%
2017               5                       11                52.3809%
2018               4                       8                 26.6667%
2018               5                       10                33.3333%
2018               7                       12                40.0000%

Is there a way I can accomplish this in one query? Thank you for looking!

Solution 1:

If window functions are available you can do this:

SELECT
    demorateyear_101,
    LENGTH(mcrcertid_112),
    COUNT(*) AS num_of_records,
    COUNT(*) / SUM(COUNT(*) OVER (PARTITION BY demorateyear_101)) * 100 AS percentage
FROM tblproviderdetails
GROUP BY demorateyear_101, LENGTH(mcrcertid_112)
ORDER BY demorateyear_101, LENGTH(mcrcertid_112);

Otherwise a subquery is needed (where clause, if any, must match):

SELECT
    demorateyear_101,
    LENGTH(mcrcertid_112),
    COUNT(*) AS num_of_records,
    COUNT(*) / (
        SELECT COUNT(*)
        FROM tblproviderdetails AS x
        WHERE x.demorateyear_101 = tblproviderdetails.demorateyear_101
    ) * 100 AS percentage
FROM tblproviderdetails
GROUP BY demorateyear_101, LENGTH(mcrcertid_112)
ORDER BY demorateyear_101, LENGTH(mcrcertid_112);