SQL Server - Normalization Values In SQL Query

Question

question

newcoder asked Feb 22, '19

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-server c#sqlquery commands

1

10 |1200

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

WRBI commented · Feb 22, 2019 at 01:19 PM

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

0 ·

Answer 1 · 2019-02-22T14:24:48Z

KenJ answered Feb 22, '19

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

10 |1200

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

newcoder commented · Feb 22, 2019 at 04:22 PM

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

0 ·

KenJ newcoder commented · Feb 22, 2019 at 08:18 PM

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 ·

newcoder commented · Feb 22, 2019 at 05:19 PM

Why do I get this error? @KenJ

0 ·

question