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?

SELECT `postcode`, COUNT( * ) AS Count1 FROM  `table1` GROUP BY  `postcode`;
SELECT `postcode`, COUNT( * ) AS Count2 FROM  `table2` GROUP BY  `postcode`;

Open in new window

Pratima

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

tigin44

a type error in previous post try this...

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.

ASKER CERTIFIED SOLUTION

Pratima

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AdrianSmithUK

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.

AdrianSmithUK

ASKER

Dear Tigin44

I tested your solution and got the following error:

#1582 - Incorrect parameter count in the call to native function 'ISNULL'