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