Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

MySQL: How do I show the count of two tables in one query.

Posted on 2011-10-05
9
Medium Priority
?
394 Views
Last Modified: 2012-08-13
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

0
Comment
Question by:AdrianSmithUK
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 36916040
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
 
LVL 15

Expert Comment

by:Eyal
ID: 36916045
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
 
LVL 26

Expert Comment

by:tigin44
ID: 36916069
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 26

Expert Comment

by:tigin44
ID: 36916076
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
 
LVL 26

Expert Comment

by:tigin44
ID: 36916093
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
 

Author Comment

by:AdrianSmithUK
ID: 36916161
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
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 2000 total points
ID: 36916349
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
 

Author Closing Comment

by:AdrianSmithUK
ID: 36923314
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
 

Author Comment

by:AdrianSmithUK
ID: 36923321
Dear Tigin44

I tested your solution and got the following error:

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

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
Blockchain technology enhances society similar to the Internet. Its effects are broad, disruptive, and will boost global productivity.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question