CODEWITHSUNDEEP
phpmysqllaraveleloquent
Jovs
Jovs

Reputation: 892

Group by if two column in different row has the same value?

I want to groupby the column1 then count it. Now if the column1 and column2 has same value in other row then it will count only as one

example table

column1  column2
2        4
1        1
1        1
3        4
2        4
3        1
1        3

example output

column1  column2  count
2        4        1
1        3        2
3        4        2

As you can see the 2 in column1 is only counted as 1 because the column2 is also same in other row.

How can I do that in mysql or eloquent query?

Upvotes: 0

Views: 568

Answers (3)

ScaisEdge
ScaisEdge

Reputation: 133360

seems you need the count for distinct column1, max(column2) 

select column1, max(column2), count(*) 
from (
  select distinct column1, column2 
  from my_table  
) t  
group by  column1

Upvotes: 1

Ryan Nghiem
Ryan Nghiem

Reputation: 2438

You can try the query:

select t.column1, t.column2, count(*) as count from (
  select column1, column2 from mytables group by column1, column2
  order by column2 desc
) as t
group by t.column1

then its result

enter image description here

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

You can try below - it'll work in mysql

select column1,max(column2),count(distinct column2)
from tablename
group by column1

Upvotes: 1

Related Questions