My last columns are trying to find the percent of items that contain a L or an R, you get the idea. However, the only percent im coming up with is 0, any suggestions?

Thank You

SELECT
LINNBR As "Number",
COD As "Code",
Convert(varchar, DAT, 101) As "Date",
Left(CAGE, 2) As House,
DateDiff("d", DAT, VAL) As "Age",
Sum( Case When NBR <> 0 Then 1 End) As "Total_Count",
Avg(BW) AS "Weight",
Avg([BW]/DateDiff("d", DAT, VAL)) As "ADG",
Avg(BC) As AvgBC,
Avg(FE) As AvgFe,
Sum( Case When Stat1 = 'E' Then 1 End) As "Elites",
Avg( Case When Stat1 = 'E' Then (BWT1) End) As "Avg_Weight_E",
(Sum( Case When Defects1 like '%L%' Then 1 else 0 End)) As "%L",
100*(Sum( Case When Def like '%T%' Then 1 else 0 End)/Sum( Case When NBR <> 0 Then 1 End)) As "%T",
100*(Sum( Case When Def like '%K%' Then 1 else 0 End)/Sum( Case When NBR <> 0 Then 1 End)) As "%K",
100*(Sum( Case When Def like '%R%' Then 1 else 0 End)/Sum( Case When NBR <> 0 Then 1 End)) As "%R",
100*(Sum( Case When Def like '%V%' Then 1 else 0 End)/Sum( Case When NBR <> 0 Then 1 End)) As "%V",
100*(Sum( Case When Def like '%D%' Then 1 else 0 End)/Sum( Case When NBR <> 0 Then 1 End)) As "%D",
100*(Sum( Case When Def like '%S%' Then 1 else 0 End)/Sum( Case When NBR <> 0 Then 1 End)) As "%S"
FROM
PSBRLEVLP
WHERE
VAL <> ''
GROUP BY
LINNBR,
COD,
DAT,
Left(CAGE, 2),
DateDiff("d", DAT, VAL)

sorry, you did mean i probally have null's in the NBR field right?

0

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

the SUM returns int, so SLQ treats the calculation as integers. You are dividng numerator by denom then multiplying by 100
e.g. 100 * ( 1234 / 1500) will give 100 * ( 0) = 0
try casting as floats (you can get away with only the numerator)

.....
100.0 *( cast( Sum( Case When Def like '%T%' Then 1 else 0 End) as float) /Sum( Case When NBR <> 0 Then 1 End)) As "%T",
.....

NULLIF(NBR, 0)

for

NBR