SQL Query to select records for rows that have value = 0 in one column and a unique identifier in another column

Question

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 a separate identifier for which all its sum value is 0.0, and the account number is null. mostly from this table, I want the result to be 5104. Can someone please help, I'm new to SQL.

Answer 1

Select DISTINCT ID FROM TableName 
    GROUP BY ID
      HAVING SUM(amount)=0.0

Refresh for another condition (another column that is varchar. It must 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

Answer 2

Select DISTINCT ID FROM TableName 
GROUP BY ID
HAVING min(amount)=0.0 and max(amount)=0.0

Answer 3

:

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

fiddle

Answer 4

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 would not work in my opinion, since you could have the quantity -1 and one of 1, with the sum of 0.

Since you did not write, if you can have negative values, you must also check the minimum level.

Addition for the new restriction:

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