CODEWITHSUNDEEP
mysqlsql
abhinsit
abhinsit

Reputation: 3272

distinct value of one column based on another column mysql

Table structure

Column1 | Column2 | Column 3 | Column 4 . . .. .  Column N

Col1Val1    | Col2Val2    |  . . . . . 
Col1Val1    | Col2Val2    | . . . ..
Col1Val1    | Col2Val3    | . . . . 
Col1Val2    | Col2Val4    | .  . .
Col1Val3    | Col2Val5    | . . 
Col1Val3    | Col2Val6    | . ..

I want to query for all the distinct values of column2 for a every distinct value of column1 and its count & values

Example output should be:

Col1Val1 | Col2Val2,Col2Val3 | 2
Col1Val2 | Col2Val4          | 1
Col1Val3 | Col2Val5,Col2Val6 | 2
.
.

This is pretty much doable from the querying + application handling.
Can this/similar output be achieved via only the sql query.

Upvotes: 0

Views: 1280

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521409

You can use GROUP_CONCAT() to aggregate and count he distinct Column2 values:

SELECT
    Column1,
    GROUP_CONCAT(DISTINCT Column2),
    COUNT(DISTINCT Column2)
FROM yourTable
GROUP BY Column1

Output:

enter image description here

Demo here:

Rextester

Upvotes: 2

Fahad Anjum
Fahad Anjum

Reputation: 1256

Try this.

select Column1 , group_concat(distinct column2) ,count(distinct column2)
from your_table 
group by column1

Upvotes: 1

Related Questions