Question 20866 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL using results of a query that has distinct counts in another query

I hope someone can help with this!

I’ve used the following query to count the distinct values in column2 and column3 and filter the results:

SELECT 
column1, 
COUNT(DISTINCT(column2)), 
COUNT(DISTINCT(column3))

FROM table1
GROUP BY column1
HAVING COUNT(DISTINCT(column2)) > 1
OR COUNT(DISTINCT(column3)) > 1

The output looks something like this:

| column1  | column2  | column3 |
| -------- | -------- |         |
| abcd     |   1      |     2   |
| efgh     |   2      |     2   |
| ijkl     |   2      |     1   |

I would now like to see what these distinct values of column2 and column3 associated with each value of column1 are. So, abcd will have one distinct value from column1 but two distinct values from column2- so I’m expecting the output to have two rows for abcd, with the value in column1 repeating, and two different values in column2

Does anyone know how to go about this?

Tags:

2

Answers


  1. 0

    This sql will show you all individual values and how often they exist:

    SELECT 
COUNT(column2),
column2
FROM table1
GROUP BY column2
HAVING COUNT(column2) > 1

    This example is only for column2, but you can extend it easy to more columns.

    Login or Signup to reply.
  2. 0

    Let a subquery return the column1 values having more than one distinct column2 or column3 values.

    select distinct column1, column2, column3
from table1
where column1 in (select column1
                  from table1
                  group by column1
                  having count(distinct column2) > 1
                      or count(distinct column3) > 1)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search