CODEWITHSUNDEEP
sql
Anna
Anna

Reputation: 379

MIN and MAX for certain rows

I have a dataset (myDataset.csv) that looks like this

User      Value
Alice     2
Alice     4
Alice     3
Bob       9
Bob       7
Bob       8

... and so on for many other users/values

I would like to find the Min and Max values for each user, but I'm struggling with doing this through an SQL query(ies).

I broke down the problem and queried for all the rows for just one user (say, Alice) to get AliceDataset.csv and then ran this query to find her min and max value. So I know how to get the Min and Max.

SELECT MIN(AliceDataset.value), MAX(AliceDataset.value)
FROM AliceDataset

But how can I loop through all the users in myDataset.csv and for each of them find their min and max (potentially even using the code from this query presented above)

Upvotes: 0

Views: 1108

Answers (3)

Nagesh Mhapadi
Nagesh Mhapadi

Reputation: 152

Aggregation functions are used mostly return single value. If you want to select any column with aggregate function you need to add group by clause with that column name which you want to add in select result.

So, in your situation you want to use aggregate function on value in your result and also want to show value functioned result with User, so you need to add clause group by with User.

So query will be: 

select User,
       max(Value) as "Maximum Value",
       min(Value) as "Minimum Value"
from AliceDataset
group by User

Note:
If you want to add condition while using aggregate function, you need to use having rather than where.
For example:

select User,
       max(Value) as "Maximum Value",
       min(Value) as "Minimum Value"
from AliceDataset
group by User
having max(Value) > 3

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

use max() and min() function, you have to use group by for getting each user max and min values

select User , max(Value) mx_val,min(Value) min_val
from your_table
group by  User

Upvotes: 2

Fahmi
Fahmi

Reputation: 37473

Use group by and aggregation - this will give you user wise minimum and maximum

SELECT user, MIN(Value) as minval, MAX( Value) as maxval
FROM AliceDataset
group by user

Upvotes: 1

Related Questions