SELECT MIN and MAX across fields and aggregate by user

I have the following raw data saved in the db

id  min_price, max_price, min_x, max_x, user_id
-------------------------------------------
 1    50        200       5      null     1
 2    0         100       0      3        1
 3    150       300       0      null     1
 4    20        200       2      5        2
 5    50        200       0      5        2
 6    150       200       1      3        2

I want to create a sql query (postgres) with the following data:

min_price, max_price, min_x, max_x, user_id
    0         300       0     null   1
    20        200       0      5     2

so basically i would get the min and max for each user_id for difference fields, where null should take precedence over the actual max value, any idea on how to achieve this via sql?

Answers


You can check if NULL exists within that column using COUT(*) vs. COUNT(column):

SELECT 
   user_id,
   CASE WHEN COUNT(*) <> COUNT(max_x) THEN NULL ELSE MAX(max_x) END AS max_x
FROM vt
GROUP BY 1

A brute force solution would be:

NULLIF(MAX(COALESCE(max_x, 9999999999)), 9999999999)

Need Your Help

Printing out all defined units in JScience

java units-of-measurement jscience

We have a system where a user can provide data of any unit. The data is stored and potentially converted into any other unit of the same dimension. The is based on the JScience api.