Solved

# Finding the percent SQL syntax

Posted on 2004-09-30

Hello,

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)