I have two queries I want to combine into a single output, I can't use a UNIOn because they have different numbers of columns.
The table is ref
with fields id
refid
cellid
cat
all of which contain whole numbers
Query 1: Finds the total number of rows for each unique cellid
SELECT cellid, COUNT(*) totalcount, cat FROM rel GROUP BY cellid
Query 2: Finds the Mode (most common value) of 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 my 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
The easiest solution here is just to write a query to join the two result sets you already have. You can store the result of your queries in tmp tables and join the temp tables like so:
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;
In the second query you can change
SELECT cellid, cat
to
SELECT cellid, 0 as totalcount, cat
to make it match the columns in the first query.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With