• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 436
  • Last Modified:

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)
0
Navicerts
Asked:
Navicerts
1 Solution
 
jdlambert1Commented:
You probably have some nulls, which sets it all null. Try substituting:

NULLIF(NBR, 0)

for

NBR
0
 
NavicertsAuthor Commented:
NBR is a keyed field that wont allow Null's
0
 
NavicertsAuthor Commented:
sorry,  you did mean i probally have null's in the NBR field right?
0
What Kind of Coding Program is Right for You?

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.

 
BillAn1Commented:
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",
.....
0
 
arbertCommented:
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"
0
 
NavicertsAuthor Commented:
Cast worked great, thank you all for your input
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now