4
ID       amount account number(varchar)
5105     0.70   23423423
5105     0.0    null
5105     0.0    null
5104     0.0    null
5104     0.0    null
5104     0.0    null
5106     0.0    32132111
5106     0.0    null

I want to get the distinct id for which all of its amount value is 0.0 and account number is null . basicly from this table i want the result as 5104. Can anyone please help i am new to SQL.

John
  • 115
  • 2
  • 12
  • possible duplicate of [How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?](http://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql) – Amit Bisht Apr 25 '14 at 07:15
  • somehow its bad to add more limitations / requests into an already answered question. – Olli Apr 25 '14 at 07:55

4 Answers4

1
Select DISTINCT ID FROM TableName 
GROUP BY ID
HAVING min(amount)=0.0 and max(amount)=0.0
Jayvee
  • 10,670
  • 3
  • 29
  • 40
0
Select DISTINCT ID FROM TableName 
    GROUP BY ID
      HAVING SUM(amount)=0.0

Update for another condition( another column which is varchar. It should be null)

  Select DISTINCT ID FROM TableName 
      WHERE AnotherColumn IS NULL
        GROUP BY ID
          HAVING SUM(amount)=0.0

SQL Fiddle: http://sqlfiddle.com/#!2/145504/3

Pranav Singh
  • 17,079
  • 30
  • 77
  • 104
0

This works:

SELECT ID, MAX(amount) FROM table_name GROUP BY ID HAVING MAX(amount)=0

Here's a fiddle

CodeBird
  • 3,883
  • 2
  • 20
  • 35
0

You can use

SELECT id,
       MIN(amount) AS minumum,
       MAX(amount) AS maximum
FROM your_table
GROUP BY id HAVING minimum = 0.0
AND maximum = 0.0

a simple sum wouldn't work in my opinion as you could have an amount of -1 and one of 1, with has a sum of 0.

Since you didn't write if you can have negative values, its necessary to check for the minimum too.

Addition for the new limitation:

SELECT id,
       MIN(amount) AS min_value,
       MAX(amount) AS max_value,
       MAX(account) AS max_account
FROM your_table
GROUP BY id 
HAVING min_value = 0.0
AND max_value = 0.0
AND max_account IS null
Olli
  • 1,708
  • 10
  • 21