Linked Questions

Popular Questions

I have a table structure like:

id  | ex_name | att_name 
10  | David   | sam G&G   
12  | John    | mark hol     
13  | John    | john b      
14  | Mark    | john c    
15  | David   | mark hol   
16  | David   | mark hol      
17  | Mark    | sam G&G     
18  | John    | john b      
19  | David   | sam G&G      
20  | John    | sam G&G

When I'm using below query:

SELECT att_name
     , count(att_name) as att_count 
  FROM `tablename` 
 group 
    by att_name 
 order 
    by att_count desc

Returns:

sam G&G = 4
mark hol = 3
john b = 2
john c = 1

I want top values of output i.e sam G&G which is 4

Same with column ex_name it returns:

David = 4
john = 4
mark = 2

I want top values of the ex_name column which is David and John having count 4

What I want the final output like :

ex_name | att_name | ex_count | att_count
David     Sam G&G     4          4
John                  4

I'm also tried below query to fetch the output but in this case, I get ex_name and att_name is NULL.

SELECT a.att_name,b.att_name,max(a.ex_count),max(b.att_count)
FROM application_data
INNER JOIN (
    SELECT ex_name,count(ex_name) as ex_count
    FROM application_data
    GROUP BY ex_name
) a 
INNER JOIN (
    SELECT att_name ,count(att_name) as att_count
    FROM application_data
    GROUP BY att_count
) b

It returns:

ex_name | att_name | ex_count | att_count
NULL      NULL        4           4

Can you help me out?Thanks in advance

Related Questions