SQL aggregate and filter functions

Question

Consider following table:

Number | Value
1        a
1        b
1        a
2        a
2        a
3        c
4        a
5        d
5        a

I want to choose every row, where the value for one number is the same, so my result should be:

Number | Value
2        a
3        c
4        a

I manage to get the right numbers by using nested SQL-Statements like below. I am wondering if there is a simpler solution for my problem.

SELECT 
a.n,
COUNT(n)
FROM 
(
SELECT number n , value k 
FROM testtable
GROUP BY number, value
) a
GROUP BY n
HAVING COUNT(n) = 1

Huh? I don't understand your logic. Where does 3/c come from? And your query returns two columns as *numbers* so it is nothing like your result set. — Gordon Linoff, Feb 28 '20 at 12:55
Because there is only one specific value for number 3. I want the number 3 to be in my result set. I want to exclude all numbers where two different values exist. — moons, Feb 28 '20 at 12:57
@GordonLinoff 3/c is the onlyNumber-Value-Combination for 3, just like 4/a. For 1 you have 1/a and 1/b so that is not a solution for the request, — Ocaso Protal, Feb 28 '20 at 12:57
Do you want to get `1` in the second column (like with your query), or the actual value (like with the responses so far)? @GordonLinoff, yes, for `2` the only value is `a`, it doesn't matter that it is twice. — Andrew, Feb 28 '20 at 13:09
@GordonLinoff exactly: the only combination is 2/a, even if this combination comes twice — Ocaso Protal, Feb 28 '20 at 13:21

Arun Palanisamy · Accepted Answer · 2020-02-28T13:06:58.443

5

You can try this

SELECT NUMBER,MAX(VALUE) AS VALUE FROM TESTTABLE
GROUP BY NUMBER
HAVING MAX(VALUE)=MIN(VALUE)

edited Feb 28 '20 at 13:06

answered Feb 28 '20 at 12:57

Arun Palanisamy

5,281
6
28
53

Interesting approach. I would just give the second column a name: `MAX(VALUE) AS Value`. – Andrew Feb 28 '20 at 13:00
This seems really helpful! Thanks! :) – moons Feb 28 '20 at 13:07
In this approach you can [set collation](https://stackoverflow.com/questions/4503703/sql-server-understanding-how-mintext-works) also. – Leszek Mazur Feb 28 '20 at 13:25

score 3 · Answer 2 · answered Feb 28 '20 at 12:58

3

You can try also this:

SELECT DISTINCT t.number, t.value
FROM testtable t
LEFT JOIN testtable t_other
  ON t.number = t_other.number AND t.value <> t_other.value
WHERE t_other.number IS NULL

answered Feb 28 '20 at 12:58

Leszek Mazur

2,443
1
14
28

score 2 · Answer 3 · answered Feb 28 '20 at 13:16

2

Another alternative using exists.

select distinct num, val from testtable a
where not exists (
  select 1 from testtable b
  where a.num = b.num
  and a.val <> b.val
)

http://sqlfiddle.com/#!9/dd080dd/5

answered Feb 28 '20 at 13:16

Ben Thurley

6,943
4
31
54

SQL aggregate and filter functions

3 Answers3