CODEWITHSUNDEEP
mysql
user3768541
user3768541

Reputation: 119

select query based on different criteria

Lets say I have a table that looks like the one below. I want to query the table based on two criterias. If the id has a count that is negative I want to get the maximum results based on that id . Also if an id only has positive numbers than I want to get the min based on that id.

id         tcount
---        ------
 1           -5
 1           -10
 1            5
 2           20
 2           30
 3           -40
 3           -50

So the results I would want to get is this

id         tcount
--         -----
 1           -5
 2           20
 3          -40

My query would look something like this and I would need to add more logic to it

SELECT id, max(count) as tcount
FROM atable
WHERE count < 0
GROUP by id

Upvotes: 0

Views: 47

Answers (1)

Barranka
Barranka

Reputation: 21047

Let's see if this works. To get the minimum/maximum negative and the minimum/maximum positive, case ... end can help you:

select id
     , min(case when tcount < 0 then tcount end) -- Minimum negative
     , max(case when tcount < 0 then tcount end) -- Maximum negative
     , min(case when tcount >= 0 then tcount end) -- Minimum non-negative
     , max(case when tcount >= 0 then tcount end) -- Maximum non-negative
     , min(tcount)
     , max(tcount)
from atable
group by id;

After reading again your question (took me two or three readings), I figured out that you want the number that is closer to zero for each group. So:

select id
     , min(abs(tcount)) * if(min(tcount) = min(abs(tcount)), 1, -1)
from atable
group by id;

The explanation:

min(abs(tcount)) wil get the minimum of the absolute value of tcount (that is, the one that's closest to zero). All that remains is to get the proper sign for the number, and that's what that if() is for: If the minimum of the absolute value of the number is equal to the minimum of the number, then the number is positive (because that only can happen if min(tcount) is greater than zero, or equal to zero), in other case, the number is negative.

Upvotes: 1

Related Questions