MySQL FULL JOIN with COUNTs and GROUP BY -

i have table this:

| id | col1     | col2     | col3  | valid | | 1  | apple    |          |       | 1     | | 2  | lemon    | apple    |       | 1     | | 3  | lemon    | pear     |       | 1     | | 4  | carrot   | apple    |       | 1     | | 5  | potatoes | tomatoes | apple | 1     | | 6  | banana   |          |       | 0     | | 6  | banana   |          |       | 1     | 

i need create mysql query, returns counts each entry in each column. ideally like:

| apple    | 1    | 2    | 1 | carrot   | 1    | null | null | lemon    | 2    | null | null | pear     | null | 1    | null | potatoes | 1    | null | null | tomatoes | null | 1    | null | banana   | 1    | null | null 

the format could different, result should show how many times appear value in column.

it should done 1 query, can't use other languages, other subresults etc...., just:

    (one query) `input` -> `output`. 

my best try is:

select *       (select col1, count(col1) sometable valid = 1 group col1) t1 left join (select col2, count(col2) sometable valid = 1 group col2) t2     on t1.col1 = t2.col2 left join (select col3, count(col3) sometable valid = 1 group col3) t3     on t1.col1 = t3.col3 

but left join loose of rows (with value in 2nd or 3rd col), tried several different "join-s", success in mysql

edit: values dynamic, don't know exact unique possibilities before

you on right track, have 1 more subquery, list possible values 3 columns using union distinct , need subqueries counts each cols separately:

select f.fruits, m1.fruit_count1, m2.fruit_count2, m3.fruit_count3     (select distinct col1 fruits mytable       union      select distinct col2 mytable        union      select distinct col3 mytable) f left join     (select col1, count(col1) fruit_count1 mytable valid=1 group  col1) m1 on f.fruits=m1.col1 left join     (select col2, count(col2) fruit_count2 mytable valid=1 group  col2) m2 on f.fruits=m2.col2 left join     (select col3, count(col3) fruit_count3 mytable valid=1 group  col3) m3 on f.fruits=m3.col3;