question

newcoder avatar image
newcoder asked

SQL Server - Normalization Values In SQL Query

Hi. I want to normalize my vales coming from where caluse. I have an SQL table and it has 3 columns and 200k+ rows. Normalize formula: ((Value / Maximum Value) * 100). I coded it like that;

string myQuery = "select c1, c2, c3 / m.max_c3 * 100 normalizedvalue from myTable inner join (select convert(float, max(c3)) c3 from myTable) m on 1 = 1 WHERE c1='" + sr[mssi1] + "' AND c2='" + sr[mssi2] + "' OR c2='" + sr[mssi1] + "' AND c1='" + sr[mssi2] + "'";


Actually it works but it's not what I want. Because it selects maximum value from myTable's maximum value. But I want to select maximum value according to where clauses results. Should I use over () or something else? Thank you.

sql-serverc#sqlquerycommands
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Please provide an example table, related data and example output.

0 Likes 0 ·

1 Answer

·
KenJ avatar image
KenJ answered

You limit the max value the same way you limit the rest of the query - with a where clause. You can include a duplicate where clause within the subquery to ensure you are only seeing the max value for the conditions you specify

string myQuery = 
"select c1, c2, c3 / m.max_c3 * 100 normalizedvalue 
from myTable 
inner join (
	select convert(float, max(c3)) max_c3 
	from myTable 
        where c1='" + sr[mssi1] + "' 
          and c2='" + sr[mssi2] + "' 
           or c2='" + sr[mssi1] + "' 
          and c1='" + sr[mssi2] + "'
   ) m on 1 = 1 
where c1='" + sr[mssi1] + "' 
  and c2='" + sr[mssi2] + "' 
  or c2='" + sr[mssi1] + "' 
  and c1='" + sr[mssi2] + "'";
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hi. It gives error. System.Data.SqlClient.SqlException: 'Ambiguous column name 'c3'. Invalid column name 'max_c3'.'. @KenJ

0 Likes 0 ·

you have the following alias in the subquery: convert(float, max(c3)) c3

You probably meant to alias this column as max_c3 which should fix both of the errors

0 Likes 0 ·

Why do I get this error? @KenJ

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.