MySql Group By Count item count Distinct

I have a table looks like

id  user_id  level

1     1        1
2     1        2
3     2        1
4     3        1

Here user id 1 exist in level 1 and level 2 Now when we count group by level then in counting we want ignore user_id 1 from level 1 cause it exist another group. we want to consider only one group existing and higher group.

I have done only group count but cant understand how ignore counting.

My current query is

select 'level', DB::raw('count(*) as total') from table GROUP BY level

My Query return counting

level      total

1             3
2             1

But I want looks like

level      total

1             2
2             1

Answer

I used this query and this gets the desired output check it out:

select T2.MLevel, COUNT(*) as total
  from (select  max(T1.level) as MLevel
    from YourTable as T1
      group by T1.user_id ) as T2
group by T2.MLevel

Advertisement

Answer