SQL Count(*) on multiple tables

[ILikeBread]

Hi all,

 

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

 

 

   

12

   

0

 

 

   

13

   

0

 

 

   

14

   

0

 

 

   

15

   

0

 

 

   

16

   

0

 

 

   

18

   

0

 

 

   

19

   

0

 

 

 

Is anyone able to assit me please

 

Thanks

[printf]

Instead of UNION, UNION ALL

[ILikeBread]

Hi printf thanks for the reply.

 

I thought of UNION ALL as well however it returns the exact same result set.

 

Anymore ideas?

[printf]

I guess you you didn't understand what I meant, so here is an example...

 

 

SELECT horse, SUM(IF(win>0, 1, 0)) AS total
FROM 
((SELECT `Horse Number` AS horse, `Win $` AS win FROM `races`.`2009`)
UNION ALL
(SELECT `Horse Number` AS horse, `Win $` AS win FROM `races`.`2010`)) AS junk GROUP BY horse ORDER BY SUM(win);

 

change all the SUM(???) to COUNT(win) if your table isn't the way I am thinking it is!

[ILikeBread]

Thanks printf. You are right, i didn't fully understand what you meant.