SQL count or max (sqlite)-CodePudding

The table is like this:

ratio|user
  0.1|2
  0.3|2
  1  |2
  1  |2
  0.4|3
  0.7|3

The query should return, for each user, either the number of times the ratio is 1, or, if there is no 1 ratio for that user, the highest ratio. So given the table above, the query should return:

2|2
3|0.7

because there are two "1" values for ratio for user 2, and the highest ratio for user 3 is 0.7.

What I have for now is select user,count(ratio) from table where ratio = 1 group by user; but obviously that fails at the second part of the requirement. Should I make a sub-table already populated with the result of that first query...?

CodePudding user response：

Use a CASE expression to choose between the count and the highest value:

SELECT user, CASE WHEN SUM(ratio = 1) > 0
                  THEN SUM(ratio = 1) ELSE MAX(ratio) END AS ratio
FROM yourTable
GROUP BY user;

CodePudding user response：

I think this can be done in 2 steps like:

select user,count(ratio) as column2 from table where ratio = 1 group by user
union 
select user,max(ratio) as column2 from table where ratio <> 1 group by user