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)