Navicerts
asked on
Finding the percent SQL syntax
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)
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)
ASKER
NBR is a keyed field that wont allow Null's
ASKER
sorry, you did mean i probally have null's in the NBR field right?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Possibly SQL Server's implicit conversion on the data type--try this:
100*(Sum( Case When Def like '%T%' Then 1 else 0 End)/Sum( Case When NBR <> 0 Then 1.00 End)) As "%T"
100*(Sum( Case When Def like '%T%' Then 1 else 0 End)/Sum( Case When NBR <> 0 Then 1.00 End)) As "%T"
ASKER
Cast worked great, thank you all for your input
NULLIF(NBR, 0)
for
NBR