Combine two queries with different number of columns

Question

I have two queries that I want to combine into one output, I can not use UNIOn, because they have a different number of columns.

ref table with fields id refid cellid cat all of which contain integers

Query 1: Finds the total number of rows for each unique cellid

 SELECT cellid, COUNT(*) totalcount, cat FROM rel GROUP BY cellid 

Query 2: search mode (most common value) cat for each unique cellid

 SELECT cellid, cat FROM rel t GROUP BY cellid, cat HAVING cat = ( SELECT cat FROM rel WHERE cellid = t.cellid GROUP BY cat ORDER BY COUNT(*) DESC, cat LIMIT 1 ) 

To give an example of what I'm trying to do, I want to query a table

 id | refid | cellid | cat 1 | 1 | 1 | 1 2 | 2 | 2 | 2 3 | 3 | 3 | 4 4 | 1 | 1 | 2 5 | 2 | 1 | 2 6 | 3 | 1 | 3 7 | 1 | 2 | 2 8 | 1 | 1 | 2 

and return

 cellid | no_of_rows | Mode_of_cat 1 | 5 | 2 2 | 2 | 2 3 | 1 | 4 

Answer 1

The easiest solution is to simply write a query to join the two result sets that you already have. You can save the result of your queries in tmp tables and join the tempo tables as follows:

 SELECT tmp1.cellid, tmp1.rows, tmp2.mode_cat FROM ( SELECT cellid, COUNT(*) AS rows FROM rel GROUP BY cellid )tmp1 LEFT JOIN ( SELECT cellid, cat AS mode_cat FROM rel t GROUP BY cellid, cat HAVING cat = ( SELECT cat FROM rel WHERE cellid = t.cellid GROUP BY cat ORDER BY COUNT(*) DESC, cat LIMIT 1 ) )tmp2 ON tmp1.cellid = tmp2.cellid; 

Answer 2

In the second query, you can change

 SELECT cellid, cat 

to

 SELECT cellid, 0 as totalcount, cat 

so that it matches the columns in the first query.