Avatar of AdrianSmithUK
AdrianSmithUK
 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

MySQL ServerDatabases

Avatar of undefined
Last Comment
AdrianSmithUK

8/22/2022 - Mon
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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
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.


⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Pratima

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
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'