SQL Count(*) on multiple tables

Question

I am writing a horse racing web app and i have a stats page. What i am trying to do is show how many winners there are from each year.

Currently i have this:

SELECT `Horse Number`, Count(*) AS `Total Winners`
FROM `races`.`2009`
WHERE `Win $`>0 
GROUP BY `Horse Number`
ORDER BY Count(*) DESC;

It works like a charm and returns the following

Horse Number | Total Winners
1|48
2|49
3|39
4|31
5|26
6|31
7|21
8|25
9|31
10|16
11|16
12|20
13|9
14|8
15|6
16|3
17|3
18|2
19|2

I have now created a table for 2010 and i am wanting SQL to return something similar but i want it to search over 2009 and 2010 at the same time. I thought something like this might do the trick.

SELECT `Horse Number`, Count(*) AS `Total Winners`
FROM `races`.`2009`
WHERE `Win $`>0 
GROUP BY `Horse Number`
UNION
SELECT `Horse Number`, Count(*) AS `Total Winners`
FROM `races`.`2010`
WHERE `Win $`>0 
GROUP BY `Horse Number`
ORDER BY Count(*) DESC;

But it is just returning extra results at the bottom of the table so i now have 2 rows for each horse like this

Horse Number | Total Winners
1|48
2|49
3|39
4|31
5|26
6|31
7|21
8|25
9|31
10|16
11|16
12|20
13|9
14|8
15|6
16|3
17|3
18|2
19|2
1|0
2|0
3|0
4|0
5|0
6|0
7|0
8|0
9|0
10|0
11|0
12|0
13|0
14|0
15|0
16|0
17|0
18|0
19|0

Is anyone able to assist me please

Thanks

Storing your result in a different table for each year is not the SQL way. You should use a single table with a year column with an index on that column. This will make things a lot easier for you. — Mark Byers
– Mark Byers, Commented Jan 1, 2010 at 8:36

DVK · Accepted Answer · 2010-01-01 09:06:23Z

10

First off, I would suggest having ONE table with an extra column for a year.

Second, with the current table structure, you can do

SELECT `Horse Number`, Count(*) AS `Total Winners`
FROM (
  SELECT * FROM `races`.`2009`
  UNION ALL
  SELECT * FROM `races`.`2010`
) all_races
WHERE `Win $`>0 
GROUP BY `Horse Number`
ORDER BY Count(*) DESC;

edited Jan 1, 2010 at 9:06

answered Jan 1, 2010 at 8:40

DVK

130k33 gold badges219 silver badges337 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mark Byers Over a year ago

You want UNION ALL there, not UNION.

DVK Over a year ago

Yep, was somehow assuming rows are unique across 2 tables which could be false. Fixed

Rob Farley Over a year ago

Can you use square brackets instead of apostrophes for your columns please? It's way more preferred, as 'Horse Number' could easily be interpreted as a string in your outer query.

DVK Over a year ago

@Rob - I never heard of square brackets used that way, is that standard transact SQL fare? BTW, I was merely cut/n/pasting OP's query here, theres lots of differences to my own style :)

Collectives™ on Stack Overflow

SQL Count(*) on multiple tables

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related