CODEWITHSUNDEEP
mysqlsqlpostgresql-9.1
Abishek Arumugam
Abishek Arumugam

Reputation: 35

Apply Multiplication on many intervals on a single table

My data consists of 1024 rows and 2 columns.Its structure looks like

enter image description here

I want to select three intervals from a single table based on first column. In that selected intervals second column values should be multiplied by values

SELECT * FROM del 
   WHERE wavelength BETWEEN 341 AND 348 
         OR wavelength BETWEEN 551 AND 664  
         OR wavelength BETWEEN 998 AND 1021

In this one, intervals are selected using this query and

After multiplication i want to display the wavelength and multiplied reflectance values in second column

Upvotes: 0

Views: 158

Answers (2)

Deepika Janiyani
Deepika Janiyani

Reputation: 1477

As the first and third range is to be multiplied by 0.25 and the second by 0.5

you can use the below query.

SELECT wavelength, 
       ( reflect * 0.25 ) AS reflect 
FROM   wavelength 
WHERE  ( wavelength BETWEEN 341 AND 348 ) 
        OR ( wavelength BETWEEN 998 AND 1021 ) 
UNION ALL 
SELECT wavelength, 
       ( reflect * 0.5 ) AS reflect 
FROM   wavelength 
WHERE  wavelength BETWEEN 551 AND 664;

if all three are to be multiplied by different values you can use 

SELECT wavelength, 
       ( reflect * 0.25 ) AS reflect 
FROM   wavelength 
WHERE  wavelength BETWEEN 341 AND 348 
       OR wavelength BETWEEN 998 AND 1021 
UNION ALL 
SELECT wavelength, 
       ( reflect * 0.5 ) AS reflect 
FROM   wavelength 
WHERE  wavelength BETWEEN 551 AND 664 
UNION ALL 
SELECT wavelength, 
       ( reflect * 0.25 ) AS reflect 
FROM   wavelength 
WHERE  wavelength BETWEEN 998 AND 1021

demo at http://sqlfiddle.com/#!2/2c41b/10

Upvotes: 2

sureshhh
sureshhh

Reputation: 1216

Try this:

SELECT wavelength, 
       CASE WHEN wavelength BETWEEN 551 AND 664 THEN reflectance * .5
            ELSE  reflectance * .25 
       END AS 'REFLECTANCE'
FROM del 
WHERE wavelength BETWEEN 341 AND 348 
   OR wavelength BETWEEN 551 AND 664  
   OR wavelength BETWEEN 998 AND 1021

or a common select:

SELECT wavelength, 
       CASE WHEN (wavelength BETWEEN 341 AND 348) 
              OR (wavelength BETWEEN 998 AND 1021) THEN reflectance * .25 
            WHEN wavelength BETWEEN 551 AND 664 THEN reflectance * .5
            ELSE 1 ---(PUT YOUR DEFAULT) 
       END AS 'REFLECTANCE'
FROM del

Upvotes: 2

Related Questions