asked on
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.
Here is an example of the desired output.
Postcode Count1 Count2
SW 200 57
LS 400 0
PH 250 23
etc...
Can you help?
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.
Here is an example of the desired output.
Postcode Count1 Count2
SW 200 57
LS 400 0
PH 250 23
etc...
Can you help?
SELECT `postcode`, COUNT( * ) AS Count1 FROM `table1` GROUP BY `postcode`;
SELECT `postcode`, COUNT( * ) AS Count2 FROM `table2` GROUP BY `postcode`;
MySQL ServerDatabases
Last Comment
AdrianSmithUK
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`
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`
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
FROM tableA A ON A.postcode = B.postcode
LEFT OUTER JOIN tableB B
a type error in previous post try this...
SELECT A.postcode, COUNT(A.postcode) AS countA, ISNULL(COUNT(B.postcode),0
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
FROM tableA A ON A.postcode = B.postcode
LEFT OUTER JOIN tableB B
a problem with the internet connection
this one is correct...
SELECT A.postcode, COUNT(A.postcode) AS countA, ISNULL(COUNT(B.postcode),0
FROM tableA A
LEFT OUTER JOIN tableB B ON A.postcode = B.postcode
GROUP BY A.postcode
this one is correct...
SELECT A.postcode, COUNT(A.postcode) AS countA, ISNULL(COUNT(B.postcode),0
FROM tableA A
LEFT OUTER JOIN tableB B ON A.postcode = B.postcode
GROUP BY A.postcode
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.
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.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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.
Great solution. Takes about 300ms to run.
Many thanks.
ASKER
Dear Tigin44
I tested your solution and got the following error:
#1582 - Incorrect parameter count in the call to native function 'ISNULL'
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`