mySQL query - unique value using GROUP and HAVING-CodePudding

here's my sample data:

id  name      source
--------------------------
1   aaa       source1
2   bbb       source1
3   ccc       source1
4   ddd       source1
5   aaa       source2
6   bbb       source2
3   ccc       source2
4   ddd       source2
7   eee       source2

I'm trying to query all records where the value from "name" only occurs once (is unique) - so the desired output would be:

id  name      source
--------------------------
7   eee       source2

I've tried a combination of GROUP BY name and HAVING but it didn't work

SELECT name, source, COUNT(*) AS total FROM data
GROUP BY name
HAVING total=1

any ideas?

PS: how can i filter for "source"? WHERE source = "source2" won't help ..

CodePudding user response：

Your query works but with a FULL_GROUP_By

all columns that are not in the select need a aggregation function

CREATE TABLE data (
  `id` INTEGER,
  `name` VARCHAR(3),
  `source` VARCHAR(7)
);

INSERT INTO data
  (`id`, `name`, `source`)
VALUES
  ('1', 'aaa', 'source1'),
  ('2', 'bbb', 'source1'),
  ('3', 'ccc', 'source1'),
  ('4', 'ddd', 'source1'),
  ('5', 'aaa', 'source2'),
  ('6', 'bbb', 'source2'),
  ('3', 'ccc', 'source2'),
  ('4', 'ddd', 'source2'),
  ('7', 'eee', 'source2');

SELECT name, MIN(source), COUNT(*) AS total FROM data
GROUP BY name
HAVING total=1

name | MIN(source) | total
:--- | :---------- | ----:
eee  | source2     |     1

db<>fiddle here

CodePudding user response：

You need to add source to the group by or remove it from the select or put it into a aggregate function otherwise it will throw a error.