Count how many times a value appears in sql query using php

Question

I have created a database and website that will be used by football managers to select their team etc. Once a match has been completed events will be stored in the match_players table. Such events are Goal, Yellow Card, Red Card etc. I have no problem getting this information into php from SQL db.

I need to add up how many times a Goal appears (a '1' is placed in the SQL table) and for what team so that a final score can be displayed. So, for example, if Team A has 1 goal and Team B has 2 then I need to display that. I am trying to count the amount of times that a Goal is registered in the table. Any help will be greatly appreciated.

Look into COUNT and GROUP BY: dev.mysql.com/doc/refman/5.0/en/… — jeroen
– jeroen, Commented Mar 20, 2013 at 14:27

Hanky Panky · Accepted Answer · 2013-03-20 14:27:18Z

1

You can use MYSQL SUM

select SUM(Goal) from match_players where Team="A"

Or you can get the same for all teams by

select Team,SUM(Goal) from match_players group by Team

answered Mar 20, 2013 at 14:27

Hanky Panky

46.9k9 gold badges76 silver badges95 bronze badges

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

2 Comments

Captain Payalytic Over a year ago

Not forgetting the GROUP BY

advermark Over a year ago

I would also suggest a date constraint, otherwise the Goals will add up from every game the team plays

DonCallisto · Accepted Answer · 2013-03-20 14:27:54Z

0

Why don't you demand this sum to SQL directly?

SELECT SUM(goals)
FROM match_table
WHERE team = 'Barcellona'

This should be much faster also than elaborate all data at "php-level"

If you want this detail for all teams

SELECT team,SUM(goals)
FROM match_table
GROUP BY team

answered Mar 20, 2013 at 14:27

DonCallisto

30k9 gold badges74 silver badges104 bronze badges

Comments

Husman · Accepted Answer · 2013-03-20 14:30:46Z

0

Well if you store a 1 each time a goal is scored, your table looks like this:

TeamID  goal
   1     1
   2     1
   1     1
   3     1
   2     1
   2     1
   1     1

So you just want a count of how many times a team appears in that table:

select TeamID, count(*) from table group by TeamID

Will give you

 TeamID | count(*)
     1  |  3
     2  |  3
     3  |  1

answered Mar 20, 2013 at 14:30

Husman

6,93910 gold badges32 silver badges48 bronze badges

Collectives™ on Stack Overflow

Count how many times a value appears in sql query using php

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related