In MySQL database, I have a products table containing various product data, including company.
| id | name | company |
|---|---|---|
| 1 | Product 1 | AAA |
| 2 | Another product | BBB |
| 3 | One more product | CCC |
| 4 | Item ZZZ | BBB |
| 5 | Product A | AAA |
| 6 | Product uT | DDD |
| 7 | Product 2z | AAA |
Now I want to build a query which will select all products which have a unique company - I mean a company, which appears only once in the whole table. In the above example - row 3 with company CCC and row 6 with company DDD.
How can I achieve this?
You can use count(id) over(partition by company)
select id, name, company from
(select id, name, company , count(id) over(partition by company) cnt
from products) t
where cnt = 1;
You can use GROUP BY ... HAVING clause to find solo companies, then join to them by the company name.
SELECT * FROM products p
JOIN
(SELECT company FROM products
GROUP BY company
HAVING COUNT(*) = 1) pg
ON p.company = pg.company
MySQL will actually let you do this even simpler query. This works in this circumstance because there is only one row in the group you are looking for. If however, you were looking for all the products for companies that have 2 products, the simpler version won't work anymore, as you'll only get one row back. The original query would continue to work with other HAVING clauses like HAVING COUNT(*) > 10
SELECT id, product, company
FROM products
GROUP BY company
HAVING COUNT(*) = 1
You need to group by company and then only select the rows that have one company:
SELECT `id`, `name`, `company` FROM `products` GROUP BY `company` HAVING COUNT(*)=1;