CODEWITHSUNDEEP
mysql
Andrew White
Andrew White

Reputation: 610

Finding columns with more than one unique value

I've got a table named fruits that looks like this:

+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| id              | int(11)      | NO   | PRI | NULL    | auto_increment |
| worm_id         | int(11)      | YES  | MUL | NULL    |                |
| event_id        | varchar(15)  | YES  |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+

The values in the table look like this:

+----------+-------------+-----------------+
| id       | worm_id     | event_id        |
+----------+-------------+-----------------+
|       12 |         134 | 1468036258-1426 |
|       13 |         134 | 1468036258-1426 |
|       14 |         134 | 1468036258-1426 |
|       15 |        NULL | 1468036258-1426 |
|       16 |         135 | 1468036258-1426 |
|       17 |         135 | 1468036258-1426 |
|       18 |         135 | 1468036258-1426 |
|       19 |        NULL | 1468036258-1426 |
|       20 |         134 | 1468102511-5892 |
|       21 |         134 | 1468102511-5892 |
|       22 |        NULL | 1468102511-5892 |
|       23 |         136 | 1468228612-9272 |
|       24 |         136 | 1468228612-9272 |
|       25 |        NULL | 1468228612-9272 |
+----------+-------------+-----------------+

Under each event ID, there can be a number of entries with a singular worm_id (e.g. 134, 135, etc) and sometimes some null entries.

In some of my entries - due to a bug in the underlying application - two unique worm_id are used in a single event_id (like event_id 1468036258-1426).

I'm attempting to build a query to show me a list of event_ids in the scenario that there is more than one unique numeric worm_id under a single event_id.

Here's what I've come up with so far:

SELECT DISTINCT(worm_id), event_id FROM `fruits`
WHERE worm_id IS NOT NULL
AND event_id = '1468036258-1426'
ORDER BY event_id DESC

This returns the following results:

+-------------+-----------------+
| worm_id     | event_id         |
+-------------+-----------------+
|         134 | 1468036258-1426 |
|         135 | 1468036258-1426 |
+-------------+-----------------+

I want to do this on a mass scale and return results that look like this:

+-----------------+-----------------+
| event_id        | worm_ids        |
+-----------------+-----------------+
| 1468036258-1426 | 134,135         |
+-----------------+-----------------+

Upvotes: 1

Views: 28

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

Use group_concat

SELECT 
  GROUP_CONCAT(DISTINCT worm_id) AS worm_ids,
  event_id 
FROM
  `fruits` 
WHERE worm_id IS NOT NULL 
  AND event_id = '1468036258-1426'

Demo

To view worm ids for all events you need to group your data by event id

SELECT 
  GROUP_CONCAT(DISTINCT worm_id) AS worm_ids,
  event_id 
FROM
  `fruits` 
WHERE worm_id IS NOT NULL 
GROUP BY event_id

Demo

If you want to view all events who have more than 1 distinct worm ids you could also apply filter on aggregate result set 

SELECT 
  GROUP_CONCAT(DISTINCT worm_id) AS worm_ids,
  event_id 
FROM
  `fruits` 
WHERE worm_id IS NOT NULL 
GROUP BY event_id 
HAVING COUNT(DISTINCT worm_id) > 1

Demo

Upvotes: 1

Related Questions