MySQL: How do I show the count of two tables in one query.
I have 2 queries below and I would like to join them to give a single result.
Table 1 has more postcodes than table 2 and I would like to list the super-set of all postcodes in the left hand column and if table 2 doesn't have a result for that particular postcode it would show zero.
select X.`postcode` , X.count1, Y.count2 From
(
SELECT `postcode`, COUNT( * ) AS Count1 FROM `table1` GROUP BY `postcode` ) X
inner join
(
SELECT `postcode`, COUNT( * ) AS Count2 FROM `table2` GROUP BY `postcode`) Y on X.`postcode` = Y.`postcode`
Eyal
SELECT a.`postcode`, sum( a.Count1 ) AS Count2 FROM (
SELECT `postcode`, COUNT( * ) AS Count1 FROM `table1` GROUP BY `postcode`
union all
SELECT `postcode`, COUNT( * ) AS Count1 FROM `table2` GROUP BY `postcode`) a
group by a.`postcode`
tigin44
SELECT A.postcode, COUNT(A.postcode) AS countA, COUNT(ISNULL(B.postcode, 1)) AS countB
FROM tableA A ON A.postcode = B.postcode
LEFT OUTER JOIN tableB B
SELECT A.postcode, COUNT(A.postcode) AS countA, ISNULL(COUNT(B.postcode),0) AS countB
FROM tableA A ON A.postcode = B.postcode
LEFT OUTER JOIN tableB B
tigin44
a problem with the internet connection
this one is correct...
SELECT A.postcode, COUNT(A.postcode) AS countA, ISNULL(COUNT(B.postcode),0) AS countB
FROM tableA A
LEFT OUTER JOIN tableB B ON A.postcode = B.postcode
GROUP BY A.postcode
AdrianSmithUK
ASKER
Dear pratima_mcs
I tested your solution and it is 99% there.
The only problem is that if table1 has a postcode and table2 does not the postcode does not show. Is there a way to get it so that it behaves like line 2 of the original example?
Dear Eyal
I tested your solution and it gives a list of postcodes and single column of strange results. It might be the sum of the two columns.
Dear Tigin44
I'm locked out of my database at the moment. I'll let you know the results in a short while.
Sorry for the delay. One of the solutions I tested overloaded the godaddy server and I was blocked by the network violations team. They kindly unlocked it for me.
Great solution. Takes about 300ms to run.
Many thanks.
AdrianSmithUK
ASKER
Dear Tigin44
I tested your solution and got the following error:
#1582 - Incorrect parameter count in the call to native function 'ISNULL'
(
SELECT `postcode`, COUNT( * ) AS Count1 FROM `table1` GROUP BY `postcode` ) X
inner join
(
SELECT `postcode`, COUNT( * ) AS Count2 FROM `table2` GROUP BY `postcode`) Y on X.`postcode` = Y.`postcode`