Search code examples
ms-access

Minimum value without minus Value in SQL

Column 1 Column 2 Column 3
5 -14 7
6 -20 7
7 -5 8
7 2 -1300
8 -9 8
9 -10 9
9 10 -1300
10 -15 9
10 15 -1300

With this query

select colum1, sum(column2), min(column3)
from Table 
group by column1

We get

Column 1 Column 2 Column 3
5 -14 7
6 -20 7
7 -1 -1300
8 -9 8
9 0 -1300
10 0 -1300

How can I get value 8 except of -1300 in row 3, and value 9 except of -1300 in row 5, and value 9 except of -1300 in row 6 ?

I mean in column3, I need minimum value except minus value (like -1300 replace with other minimum value)

Column 1 Column 2 Column 3
5 -14 7
6 -20 7
7 -1 8
8 -9 8
9 0 9
10 0 9

I can't use operator because it will remove the row so column2 value will be change. It won't return the proper record

Solution

  • In standard SQL syntax, you would use case:

    select column1, sum(column2),
       min(case when column3 > 0 then colum3 end)
from Table 
group by column1;

    The equivalent in MS Access is:

    select colum1, sum(colum2),
       min(iif(column3 > 0, column3, null))
from Table 
group by colum1