CODEWITHSUNDEEP
asp.netsqlvb.netvisual-studio-2010
user2190029
user2190029

Reputation: 11

Calculation error in SELECT statement

I have an SQL I am trying to create in a DatasetAdapter it works to a certain point but I cant figure out to get the last bit working.

I have a Table(MainTable) with the following Fields: ID Name Image Area Mean

I want to calculation ABS(Area-?) AS Cal1, ABS(Mean-?) AS Cal2 , Cal1+Cal2 As Cal3

SELECT 
ID, Image, Name, Area, Mean, ABS(Area - ?) AS cal1, ABS(Mean - ?) AS cal2, Cal1+Cal2 AS cal3
FROM  MainTable

I Then want to get the maximum value of Cal3

SELECT        MAX(cal3) AS maxofcal3
FROM          
  (SELECT        ID, Image, Name, Area, Mean, ABS(Area - ?) AS cal1, ABS(Mean - ?) AS cal2, derivedtbl_1.cal1 + derivedtbl_1.cal2 AS cal3
FROM            MainTable) 
derivedtbl_1

It works so far giving me the maximum value of cal3.

QUESTION: I want to carry out another calculation ( Cal3/maxofcal3). I cant figure it out. I tried code below but came up with an error:

SELECT        MAX(Cal3) AS maxofCal3, Cal3 / MaxofCal3 AS Norm
FROM            (SELECT        ID, Image, Name, Area, Mean, ABS(Area - ?) AS cal1, ABS(Mean - ?) AS cal2, derivedtbl_1.cal1 + derivedtbl_1.cal2 AS cal3
                                                    derivedtbl_1.Cal1 + derivedtbl_1.Cal2 AS Cal3
                          FROM            MainTable) derivedtbl_1
GROUP BY Cal3 / MaxofCal3.

Upvotes: 1

Views: 90

Answers (1)

jezzarax
jezzarax

Reputation: 413

Group by Cal4 only, not by Cal4 / MaxofCal4

Check the following example to get the idea how to make your code work:

CREATE TABLE #SampleTable (ID int, Image nvarchar(10), Name nvarchar(10), Area int, Mean int )


SELECT MAX(Cal3) AS maxofCal3, Cal4 / MAX(Cal3) AS Norm
FROM (
SELECT ID, Image, Name, Area, Mean, ABS(Area - 1) AS cal1, ABS(Mean - 1) AS cal2, ABS(Area - 1) + ABS(Area - 1) AS cal3, ABS(Area - 1) + ABS(Area - 1) AS Cal4 FROM #SampleTable
) derivedtbl_1 
GROUP BY Cal4


DROP TABLE #SampleTable

Upvotes: 1

Related Questions