Using SELECT queries within a COUNT()

[MargateSteve]

Its been a while but I have another bit I am stuck on that will probably be a piece of cake to you guys but has left me stumped.

 

These are the three tables I am working with

CREATE TABLE IF NOT EXISTS `goals` (
  `goal_id` int(11) NOT NULL AUTO_INCREMENT,
  `match` int(11) DEFAULT NULL,          FK to games.match_id
  `scorer` int(11) DEFAULT NULL,          FK to players.player_id
  `goal_type` int(11) DEFAULT NULL,
  `goal_time` int(11) DEFAULT NULL,
  PRIMARY KEY (`goal_id`)
)

CREATE TABLE IF NOT EXISTS `players` (
  `player_id` int(11) NOT NULL AUTO_INCREMENT,
  `surname` varchar(255) DEFAULT NULL,
  `firstname` varchar(255) DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `position` int(11) DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `date_joined` date DEFAULT NULL,
  `date_left` date DEFAULT NULL,
  `previous_clubs` varchar(255) DEFAULT NULL,
  `place_of_birth` varchar(255) DEFAULT NULL,
  `home_sponsor` varchar(255) DEFAULT NULL,
  `away_sponsor` varchar(255) DEFAULT NULL,
  `profile` longtext,
  `Triallist` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`player_id`)
)


CREATE TABLE IF NOT EXISTS `games` (
  `match_id` int(11) NOT NULL AUTO_INCREMENT,
  `date` date DEFAULT NULL,
  `time` time DEFAULT NULL,
  `competition` int(11) DEFAULT NULL,
  `round` tinyint(2) DEFAULT NULL,
  `replay` char(1) DEFAULT NULL,
  `h_a` varchar(45) DEFAULT NULL,
  `opponent` int(11) DEFAULT NULL,
  `wdl` varchar(45) DEFAULT NULL,
  `for` tinyint(4) DEFAULT NULL,
  `against` tinyint(4) DEFAULT NULL,
  `attendance` int(11) DEFAULT NULL,
  `report_url` longtext,
  `photo_url` longtext,
  `stadium` int(11) DEFAULT NULL,
  `manager` varchar(45) DEFAULT NULL,
  `live` varchar(255) DEFAULT NULL,
  `notes` varchar(255) DEFAULT NULL,
  `extra_time` char(1) DEFAULT NULL,
  PRIMARY KEY (`match_id`)
)

 

In games.competition the entry will either be 1, 2, 3 or 4 depending on which competition the game was played in (this links to another table with the competition names but that is not needed here).

I have managed to get a query that will do the job for just one competition at a time

SELECT
COUNT(goals.goal_id) AS gls1,
goals.scorer,
players.surname,
games.competition
FROM goals
INNER JOIN players ON goals.scorer = players.player_id
INNER JOIN games ON goals.match = games.match_id
WHERE games.competition = 1
GROUP BY goals.scorer

 

This works exactly how I need it to with just the goals from competition 1, but I want there to be a column for the total goals scored in each competition

I have been trying to find a way to use a SELECT statement within the COUNT along the lines of

COUNT (SELECT....WHERE games.competition = 1) AS gls1,
COUNT (SELECT....WHERE games.competition = 2) AS gls2,
COUNT (SELECT....WHERE games.competition = 3) AS gls3

but every way I have tried has failed!!

 

I have tried putting the JOINS in the COUNT (SELECT...'s and also tried leaving them where they are but every attempt has been the same, a blank page.

 

I am sure that I read somewhere before that this can be done, which is why I attempted it in the first place, but now I can find no trace.

 

Any advice would be greatfully received!

Thanks in advance

Steve

 

[awjudd]

How about having a conditional statement within your COUNTs?

 

i.e.

 

SELECT
COUNT( CASE games.competition WHEN 1 THEN goals.goal_id ELSE NULL END CASE ) AS gls1, /* NULL shouldn't be counted */
COUNT( CASE games.competition WHEN 2 THEN goals.goal_id ELSE NULL END CASE ) AS gls2,
goals.scorer,
players.surname,
games.competition
FROM goals
INNER JOIN players ON goals.scorer = players.player_id
INNER JOIN games ON goals.match = games.match_id
GROUP BY goals.scorer

 

Etc? ... or group it by competition id, then you don't need the conditional, and it would return the counts for each of the competitions, even if there were more than what you originally expected.

 

Does that make sense?

 

~judda

[MargateSteve]

I gave this a try with a bit of a tweak but all three columns showed the total goals, not just the one for the respective competition

SELECT
COUNT('CASE games.competition WHEN 1 THEN goals.goal_id ELSE NULL END CASE') AS gls1, 
COUNT('CASE games.competition WHEN 2 THEN goals.goal_id ELSE NULL END CASE') AS gls2,
COUNT('CASE games.competition WHEN 3 THEN goals.goal_id ELSE NULL END CASE') AS gls3,
goals.scorer,
players.surname,
games.competition
FROM goals
INNER JOIN players ON goals.scorer = players.player_id
INNER JOIN games ON goals.match = games.match_id
GROUP BY goals.scorer

I am presumably missing something simple here but cannot work it out!

 

Steve

 

PS. Apologies for posting this in the PHP forum initially but that was force of habit!!!

[MargateSteve]

It won't let me edit the post but there is a test page with the result at http://www.margate-fc.com/content/1st_team/stats/scorers.php

[fenway]

You probably want to SUM(), with an inside IF().

[MargateSteve]

Still no joy with SUM either I am afraid.

I have tried a mixed query of

$query_Recordset1 = "SELECT
SUM('if(games.competition = 1)') AS gls1, 
COUNT('if(games.competition = 2)') AS gls2,
COUNT('CASE games.competition WHEN 3 THEN goals.goal_id ELSE NULL END CASE') AS gls3,
goals.scorer,
players.surname,
games.competition
FROM goals, games
INNER JOIN players ON goals.scorer = players.player_id
WHERE goals.match = games.match_id
GROUP BY goals.scorer ORDER BY gls1 DESC
";

 

gls1 shows up as 0

gls2 and gls3 both show the total goals for the player, not just restricted to the goals in the specific competition.

 

The 'games' table is

CREATE TABLE IF NOT EXISTS `games` (
  `match_id` int(11) NOT NULL AUTO_INCREMENT,
  `date` date DEFAULT NULL,
  `time` time DEFAULT NULL,
  `competition` int(11) DEFAULT NULL,
  `round` tinyint(2) DEFAULT NULL,
  `replay` char(1) DEFAULT NULL,
  `h_a` varchar(45) DEFAULT NULL,
  `opponent` int(11) DEFAULT NULL,
  `wdl` varchar(45) DEFAULT NULL,
  `for` tinyint(4) DEFAULT NULL,
  `against` tinyint(4) DEFAULT NULL,
  `attendance` int(11) DEFAULT NULL,
  `report_url` longtext,
  `photo_url` longtext,
  `stadium` int(11) DEFAULT NULL,
  `manager` varchar(45) DEFAULT NULL,
  `live` varchar(255) DEFAULT NULL,
  `notes` varchar(255) DEFAULT NULL,
  `extra_time` char(1) DEFAULT NULL,
  PRIMARY KEY (`match_id`)
)

 

and a selection of the data is

INSERT INTO `games` VALUES (60, '2010-08-28', '15:00:00', 1, NULL, NULL, 'A', 17, 'L', *remaining columns after 'wdl' snipped);
INSERT INTO `games` VALUES (100, '2010-09-11', '15:00:00', 2, 2, '', 'A', 38, 'W',  *remaining columns after 'wdl' snipped));
INSERT INTO `games` VALUES (101, '2010-10-16', '15:00:00', 3, 2, NULL, 'H', 39, 'W',  *remaining columns after 'wdl' snipped));
INSERT INTO `games` VALUES (103, '2011-06-30', '19:45:00', 4, 7, '', 'H', 41, '', *remaining columns after 'wdl' snipped));
INSERT INTO `games` VALUES (106, '2010-09-25', '15:00:00', 2, 3, '', 'H', 16, 'D' *remaining columns after 'wdl' snipped));
INSERT INTO `games` VALUES (107, '2010-10-12', '19:45:00', 5, 7, '', 'H', 44, 'L', *remaining columns after 'wdl' snipped));
INSERT INTO `games` VALUES (108, '2010-09-27', '19:45:00', 2, 3, 'R', 'A', 16, 'D',  *remaining columns after 'wdl' snipped));

 

The rest of the table structure is in my original post.

 

I can fully understand what each suggestion is trying to do but none of them can seem to recognise the 'number' in the competition field.

 

I have also tried judda's suggestion of grouping by competition

$query_Recordset1 = "SELECT
COUNT(players.player_id) AS gls1, 
goals.scorer,
players.surname,
games.competition
FROM goals, games
INNER JOIN players ON goals.scorer = players.player_id
WHERE goals.match = games.match_id
GROUP BY goals.scorer, games.competition ORDER BY gls1 DESC
";

 

and although this works all the results show in one column and replicates the players name for each competition for example

Surnamegls1

Smith10

Smith4

Smith1

Jones7

Jones2

 

When I would want it to show

Surnamegls1gls2gls3

Smith1041

Jones720

 

I have had a read around to try to work out how to get the results from each competition into a separate column but am, once again, stumped!!!

 

As always thanks for your patience and any suggestions would be extremely welcome!!!

 

Steve

[awjudd]

Why are your expressions within the count a string?

 

COUNT('CASE games.competition WHEN 3 THEN goals.goal_id ELSE NULL END CASE') AS gls3,

 

It shouldn't be in quotes ... it should be:

COUNT(CASE games.competition WHEN 3 THEN goals.goal_id ELSE NULL END CASE) AS gls3,

 

~judda

[MargateSteve]

When I was using it without the quotes I was getting this error message

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'CASE) AS gls1, COUNT(CASE games.competition WHEN 2 THEN goals

 

However by leaving out the CASE after END it all works fine but has lead to two more questions......

 

Here is my current working query

$query_Recordset1 = "SELECT
COUNT(CASE games.competition WHEN 1 THEN goals.goal_id ELSE NULL END) AS lgegls,
COUNT(CASE games.competition WHEN 2 THEN goals.goal_id ELSE NULL END) AS facgls,
COUNT(CASE games.competition WHEN 3 THEN goals.goal_id ELSE NULL END) AS fatgls,
COUNT(CASE games.competition WHEN 4 THEN goals.goal_id ELSE NULL END) AS kscgls,
COUNT(CASE games.competition WHEN 5 THEN goals.goal_id ELSE NULL END) AS lgecgls,
COUNT(CASE WHEN games.competition <> 6 THEN goals.goal_id ELSE NULL END) AS tgls,
goals.scorer,
players.firstname,
players.surname,
games.competition
FROM goals, games
INNER JOIN players ON goals.scorer = players.player_id
WHERE goals.match = games.match_id AND games.date BETWEEN '2010-07-01' AND '2011-06-31' 
GROUP BY goals.scorer ORDER BY tgls DESC
";

This is working almost the way I need it to at http://www.margate-fc.com/content/1st_team/stats/scorers.php

 

The last COUNT counts all records except for those in 'competition' 6 as they are exhibition games and do not count towards the statistics and are there just for reference only. This is a bodged together way of getting the Total Goals (tgls).

I tried all manner of ways of getting this done using SUM and adding together 'lgegls', 'facgls', 'fatgls' etc but all gave me another Syntax error.

 

On top of this, although goals in 'competition' 6 should not be counted, it still shows the name of a player who only got a goal in that competition. I tried using

AND tgls > 0

but get

Unknown column 'tgls' in 'where clause'

 

So all in all, nearly there, but if anyone can give any suggestions for the last couple of bits it will also open up a lot of other possibilities within the whole site, not just this page!!

 

Thanks, as always

Steve

 

 

[MargateSteve]

Ignore most of the last post as I have got it all sorted but, presumably, not in the correct way!! However, it works so that will suit me for now!!

The final code is

$query_Recordset1 = "SELECT
COUNT(CASE games.competition WHEN 1 THEN goals.goal_id ELSE NULL END) AS lgegls,
COUNT(CASE games.competition WHEN 2 THEN goals.goal_id ELSE NULL END) AS facgls,
COUNT(CASE games.competition WHEN 3 THEN goals.goal_id ELSE NULL END) AS fatgls,
COUNT(CASE games.competition WHEN 4 THEN goals.goal_id ELSE NULL END) AS kscgls,
COUNT(CASE games.competition WHEN 5 THEN goals.goal_id ELSE NULL END) AS lgecgls,
COUNT(goals.goal_id) AS tgls,
goals.scorer,
players.firstname,
players.surname,
games.competition
FROM goals, games
INNER JOIN players ON goals.scorer = players.player_id
WHERE goals.match = games.match_id AND games.date BETWEEN '2010-07-01' AND '2011-06-31' AND games.competition <> 6
GROUP BY goals.scorer ORDER BY tgls DESC, lgegls DESC
";

moving the exclusion of 'competition' 6 from the COUNT to the WHERE.

 

However, I would still be interested if there was a way of adding the created COUNTS together (SUM lgegls + facgls) etc for future use.

 

Finally, and I promise this will be the last 2 questions on the subject.....

Is there a way of using a WHEN statement to exclude data? For example

COUNT(CASE games.competition WHEN NOT 6 THEN goals.goal_id ELSE NULL END) AS tgls

 

Also, can multiple 'CASE's' be used in a COUNT? Such as

COUNT(CASE games.competition WHEN 6 AND games.date BETWEEN '2010-07-01' AND '2011-06-31' THEN goals.goal_id ELSE NULL END) AS tgls

 

I do not need these to finalise this particular page but can see their use coming up in future pages!!

 

Steve