Help needed with using COUNT, GROUP BY and UNION together - HTML Forums

welshsteve · 04-17-2009, 07:01 AM

Hi everyone, not sure if this is possible, but I have three tables with identical structures. They are lists of snooker breaks made in three different competitions. The table structures are:

Name
Club
Break

I want to do the following:

1. Sellect all records from the three tables
2. Count the number of rows for each player
3. Group the results by Player.

The end result would be:

Name................Club...............No. of breaks
Player 1.............Team 1.................10
Player 2.............Team 2..................9
Player 3.............Team 3..................8

etc etc

This is what I have, but it doesn't work

(SELECT Name, Club, COUNT(Break) AS Breaks FROM Breaks_Prem)
UNION
(SELECT Name, Club, COUNT(Break) AS Breaks FROM Breaks_A)
UNION
(SELECT Name, Club, COUNT(Break) AS Breaks FROM Breaks_Comp)
GROUP BY Name
ORDER BY Breaks DESC , Name , Club;

Is anybody able to help?

Vege · 04-18-2009, 05:06 AM

Quote:

SELECT Name,Club,COUNT(Break) as Breaks FROM ((SELECT * FROM Breaks_Prem) UNION
(SELECT * from Breaks_A) UNION
(SELECT * FROM Breaks_Comp)) as allali group by Name;

This works if your using mysql 5.0 or higher.

welshsteve · 04-20-2009, 09:31 AM

Hi, this doesn't quite work. Here's an example of what's happening.

Player 1 makes breaks of: 55, 46, 43, 41, 40, 38, 38, 36, 35, 33, 31, 31, 31, 30, 30

That's 15 breaks. But the count is returning 13 breaks

Vege · 04-25-2009, 08:36 AM

Quote:

Originally Posted by welshsteve

Hi, this doesn't quite work. Here's an example of what's happening.

Player 1 makes breaks of: 55, 46, 43, 41, 40, 38, 38, 36, 35, 33, 31, 31, 31, 30, 30

That's 15 breaks. But the count is returning 13 breaks

I'w been away for a while and i apologize that i haven't been able to ansver.

Sorry but i don't see an error in my query. Can you post the sql to produce working example of my query not working