[SOLVED] Multiple Counts?

[cristal777]

Hi,  I am pretty new at this however now I am now completely stuck.  I have 3 tables with the following fields.

 

Fixtures

FixID

RefID

 

Referees

RefID

Referee

 

Cards

FixID

Card

 

Currently I have query that counts the number of Yellow and Red cards, grouped by the referee:

 

SELECT Fixtures.RefID,Referees.Referee
,          sum(if(Cards.Card='Yellow',1,0)) AS "Yellow Card"
,          sum(if(Cards.Card='Red',1,0)) AS "Red Card"
,          count(Cards.Card) as "Total"
FROM Referees INNER JOIN (Fixtures INNER JOIN Cards ON Fixtures.FixID = Cards.FixID) ON Referees.RefID = Fixtures.RefID
GROUP BY Fixtures.RefID, Referees.Referee
ORDER BY Total DESC;

 

The above works great, however what I want to do is also have the total number of games the Referee has done, I can do this in a seperate query:

 

SELECT Referees.RefID, Referees.Referee, Count(Fixtures.FixID) AS "NoGames"
FROM Referees
INNER JOIN Fixtures ON Referees.RefID=Fixtures.RefID
GROUP BY Referees.RefID, Referees.Referee
ORDER BY NoGames DESC;

 

However when I try and add an extra column count in the first query to count/show the number of games the Ref has done it just returns the same total number of cards i.e the the same number as the column "Total"? Like so:

 

SELECT Fixtures.RefID,Referees.Referee
,          sum(if(Cards.Card='Yellow',1,0)) AS "Yellow Card"
,          sum(if(Cards.Card='Red',1,0)) AS "Red Card"
,          count(Cards.Card) as "Total"
,          [b]count(Fixtures.FixID) as "NoGames"[/b]
FROM Referees INNER JOIN (Fixtures INNER JOIN Cards ON Fixtures.FixID = Cards.FixID) ON Referees.RefID = Fixtures.RefID
GROUP BY Fixtures.RefID, Referees.Referee
ORDER BY Total DESC;

 

Can any help, apologies if this does not make sense.

 

Thanks in advance for any help given.

 

C.

[fenway]

I'm not sure how you mean -- you're inner joining, so there will be the same # of records from both tables, and as long as the card field is NOT NULL, the counts will match.

[cristal777]

Thanks for your reply fenway.

 

Basically when I run the query I get the following:

 

RefIDRefereeYellow cardRed CardTotalNoGames

1Referee 1

34

2

3636

2Referee 2

40

8

4848

3Referee 3

18

0

1818

4Referee 4

31

2

3333

 

I get the same count/total for the columns "Total" and "NoGames"?

 

The "Total" card count is correct however I know that "Referee 1" has only referee'd 12 games, "Referee 2" 22 games etc etc

 

Apologies if I am not explaining this very well.

 

Many thanks for any help you can provide.

 

C.

[fenway]

Like I said, they're always going to be the same.

 

You could try a COUNT( DISTINCT yourField).

[cristal777]

Cool that kinda works; thanks fenway.

 

However I know "Referee 1" has ref'd 12 games however it reports/counts 10?

[fenway]

Hmmm... could you show me the output of those 12 rows/

[cristal777]

When I run this I get:

 

SELECT Fixtures.RefID,Referees.Referee
,          sum(if(Cards.Card='Yellow',1,0)) AS "Yellow Card"
,          sum(if(Cards.Card='Red',1,0)) AS "Red Card"
,          count(Cards.Card) as "Total"
,          count(DISTINCT Fixtures.FixID) as "NoGames"
FROM Referees INNER JOIN (Fixtures INNER JOIN Cards ON Fixtures.FixID = Cards.FixID) ON Referees.RefID = Fixtures.RefID
WHERE Referees.Referee = "Referee 1"
GROUP BY Fixtures.RefID, Referees.Referee
ORDER BY NoGames DESC

 

RefID

Referee

Yellow Card

Red Card

Total

NoGames

1Referee 1

34

2

3610

 

However "Referee 1" has ref'd 12 games not 10.

 

SELECT Fixtures.RefID, Referees.RefID, Referees.Referee
FROM Referees
INNER JOIN Fixtures ON Referees.RefID = Fixtures.RefID
WHERE Referees.Referee = "Referee 1"

 

Fixtures.RefID

Referees.RefID

Referee.Referee

11Referee 1

11Referee 1

11Referee 1

11Referee 1

11Referee 1

11Referee 1

11Referee 1

11Referee 1

11Referee 1

11Referee 1

11Referee 1

11Referee 1

 

It returns 12 Rows meaning he has ref'd 12 games but it is only saying 10 in my earlier query?

[fenway]

But you're counting FixID in your other query....

[cristal777]

Hmm now I am confused 

 

I use this query to count the number of games the referee has managed:

 

SELECT Referees.RefID, Referees.Referee, Count( Fixtures.FixID ) AS NoGames
FROM Referees
INNER JOIN Fixtures ON Referees.RefID = Fixtures.RefID
GROUP BY Referees.RefID, Referees.Referee
ORDER BY NoGames DESC 

 

The above works correctly, and displays the number games a referee has done?

 

Thanks for all your assistance so far fenway its much appreciated.

 

C.

[cristal777]

Ahhhhh its solved!  It was missing 2 of the games in the count as those 2 games had 0 bookings so its not getting counted.

 

Many thanks to fenway for all his help.

 

Its most appreciated!