mysql MIN and MAX functions with if statement

I have an sql query like this:

SELECT IF( (SELECT COUNT(*) FROM `test_table2` WHERE `id` = t.`id`) > 0,
    (t.`price` + (SELECT `price` FROM `attribute` WHERE `id` = t.`id` LIMIT 1)),
    t.`price`
) AS `full_price`, 
MIN(`full_price`) as `min`, MAX(`full_price`) as `max` 
FROM `test_table` t

How can i retrieve min and max values without using duplicate code in if statement?

Thanks.

Answers


Enclose the calculation of full_price in a subquery:

SELECT 
      MIN(full_price) AS min_full
    , MAX(full_price) AS max_full
FROM
  ( SELECT IF( (SELECT COUNT(*) FROM test_table2 WHERE id = t.id) > 0
             , t.price + (SELECT price FROM attribute WHERE id = t.id LIMIT 1)
             , t.price
             ) AS full_price  
    FROM test_table AS t
    WHERE ...                 --- if you want conditions applied to `test_table`
                              --- before calculating the `full_price`
  ) AS tmp ;

Improvements:

  • (standard SQL): use CASE clause and not IF which is only for MySQL.
  • (performance): change the (SELECT COUNT ...) > 0 to EXISTS (SELECT ... ). It's usually faster.

Your query would become:

SELECT 
      MIN(full_price) AS min_full
    , MAX(full_price) AS max_full
FROM
  ( SELECT 
        CASE WHEN 
               EXISTS (SELECT * FROM test_table2 WHERE id = t.id) 
             THEN t.price + (SELECT price FROM attribute WHERE id = t.id LIMIT 1)
             ELSE t.price
        END AS full_price  
    FROM test_table AS t
    WHERE ... 
  ) AS tmp ;

Need Your Help

Searching inside a USER model rails

ruby-on-rails ruby ruby-on-rails-3 ruby-on-rails-4

In my application, i have a search input that i want to use to look for users that can match what i typed inside the search input.

Viewing Python(+mod_wsgi) error messages in Chrome Network pane

python json google-chrome mod-wsgi

I'm new to Python and I'm having a problem viewing error messages: I have a javascript function making a call like: