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

AdrianSmithUKAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pratima PharandeCommented:
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`
0
EyalCommented:
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`
0
tigin44Commented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Download your Guide
tigin44Commented:
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
0
tigin44Commented:
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
0
AdrianSmithUKAuthor Commented:
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.


0
Pratima PharandeCommented:
select X.`postcode` , X.count1, Y.count2 From
(
SELECT `postcode`, COUNT( * ) AS Count1 FROM  `table1` GROUP BY  `postcode` ) X
Left join
(
SELECT `postcode`, COUNT( * ) AS Count2 FROM  `table2` GROUP BY  `postcode`) Y on X.`postcode`  = Y.`postcode`
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AdrianSmithUKAuthor Commented:
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.
0
AdrianSmithUKAuthor Commented:
Dear Tigin44

I tested your solution and got the following error:

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

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.