Percentage calculation with case not working as expected I've been racking my brain on this since yesterday afternoon, I figure it is time to call on those smarter than I. I have some data represented by the following. Sorry I have to use an image, I cannot figure out how to format to look like a table.

create table fico_scores
(
    fundingyear int,
    fico int,
    Correspondent varchar(50)
)

insert into fico_scores (fundingyear,fico,Correspondent)
        values (2017,593,'Amer Equit Mtg'),(2017,703,'Amer Equit Mtg'), 
(2018,684,'Amer Equit Mtg'),
               (2017,691,'Amer Equit Mtg'),(2017,650,'Amer Equit Mtg'),(2017,697,'Am Corp'),
               (2017,652,'Am Corp'),(2017,792,'Am Corp'),(2018,0,'Am Corp'),
               (2018,796,'Am Corp'),(2017,729,'Appr Fun Corp'),(2017,748,'Appr Fun Corp'),
               (2017,780,'Appr Fun Corp'),(2018,770,'Appr Fun Corp'),(2017,663,'Appr Fun Corp'),
               (2018,752,'Appr Fun Corp'),(2018,715,'Cacl Risk Analytics'),(2018,683,'Cacl Risk Analytics'),
               (2018,735,'Cacl Risk Analytics'),(2017,693,'Cap Mtg LLC'),(2017,624,'Cap Mtg LLC'),(2018,746,'Cap Mtg LLC'),
               (2018,666,'Cap Mtg LLC'),(2018,669,'Cap Mtg LLC')


I Have the following code, and when running it looking at only one Correspondent everything looks fine as you can see below the code.

select fundingyear,case
                when fico = 1 then 'Less than 620'
                when fico = 2 then '620-679'
                when fico = 3 then '680-719'
                when fico = 4 then '720+' end as fico,
                sum(countfico) as count_Fico, count(countfico) * 1.0 / 
           SUM(count(countfico)) OVER (PARTITION BY fundingyear) as fico_perc
FROM
(
    select fundingyear, case 
                    when fico &lt; 620 then 1
                    when fico &gt;= 620 and fico &lt;= 679 then 2
                    when fico &gt;= 680 and fico &lt;= 719 then 3
                    when fico &gt;= 720 then 4
                    end as fico, count(fico) as countfico
    from vw_LoanRpt
    where Correspondent = 'Am Corp'
    group by fico,FundingYear
) t
group by FundingYear,fico




As you can see the percentages line up with the counts correctly.
But when I run the code to view all correspondents it looks really different.



My question is, what am I doing incorrectly that is causing such a skew in the calculations?

Again sorry for using images so much. I will learn this formatting stuff eventually.

TYIA!

                    2017          2018
Less Than 620      521 | 14%    499 | 13%
620 - 679         2027 | 28%   2619 | 31%
680 - 719         1260 | 21%   1661 | 20%
720+              2137 | 37%   3320 | 35%
Quick-n-dirty dual/CTE solution:

-- sum up the fico groups

with fico_groups as
(select   fundingyear,
          case  when fico &lt;  620                 then '0-619'
                when fico &gt;= 620 and fico &lt;= 679 then '620-679'
                when fico &gt;= 680 and fico &lt;= 719 then '680-719'
                when fico &gt;= 720                 then '720+'
          end as fico_txt,
          count(fico) as count_fico
 from     fico_scores
 group by fundingyear,
          case  when fico &lt;  620                 then '0-619'
                when fico &gt;= 620 and fico &lt;= 679 then '620-679'
                when fico &gt;= 680 and fico &lt;= 719 then '680-719'
                when fico &gt;= 720                 then '720+'
          end
),

-- get the yearly totals

fico_totals as
(select   fundingyear,
          sum(count_fico) as fico_total
 from     fico_groups
 group by fundingyear
)

-- final result set

select fg.fundingyear,
       fg.fico_txt                           as fico,
       fg.count_fico,
       (fg.count_fico *1.0 / ft.fico_total)  as fico_perc

from   fico_groups fg,
       fico_totals ft

where  fg.fundingyear = ft.fundingyear

order by fg.fundingyear, fg.fico_txt


Result set (based on sample data provided by OP):

fundingyear   fico      count_fico  fico_perc
2017          0-619     1           0,076923076923
2017          620-679   4           0,307692307692
2017          680-719   4           0,307692307692
2017          720+      4           0,307692307692
2018          0-619     1           0,090909090909
2018          620-679   2           0,181818181818
2018          680-719   3           0,272727272727
2018          720+      5           0,454545454545


Here's a db fiddle of the above.