How to count rows in MYSQL table with multiple column criteria?

Question

I have a table to football results in a mysql table and want to count how many games have been played by season and game type.

For example the table looks like this

+---------------+-------------+----------+
|   Type        |  Season     |   Goals  |
+---------------+-------------+----------+
|   Club        |    11       |    4     |
+---------------+-------------+----------+
|International  |    12       |    5     |
+---------------+-------------+----------+
|   Club        |    12       |     0    |
+---------------+-------------+----------+

What I want to do is total the number of club games played in season 12 so that would result in 1.

the current MYSQL code I have is

"SELECT COUNT * FROM table WHERE season='12' AND type='club'"

and that isn't working as I think I am using the wrong function. What is the correct function for the desired output?

Hello Aasim, are the results incorrect or you get an error? If you are getting an error you should use "COUNT(*)" instead of "COUNT *". If you do not get an error, can you specify what data you get with the above SQL statement? — Menno
– Menno, Commented Mar 7, 2014 at 10:18

uvais · Accepted Answer · 2014-03-07 10:58:33Z

1

try this

 SELECT COUNT(*) total FROM `<tablename>` WHERE season=12 AND type='club';

make sure your connection established:

 $sql = "SELECT COUNT(*) total FROM cristiano WHERE season=12 AND which='club'"
 if ($result=mysql_query($sql)) {
    while($row=mysql_fetch_row($result)) {
     echo $row['total'];
    }
 } else {
   echo "Error" . mysql_error();
   }

edited Mar 7, 2014 at 10:58

answered Mar 7, 2014 at 10:22

uvais

4162 silver badges6 bronze badges

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

4 Comments

uvais Over a year ago

you need to provide your table name whatever it is ok

uvais Over a year ago

try again and let me know if error comes again ,, also remove quotes around season number

Aasim Azam Over a year ago

its not returning any errors now but how would I echo the result? For a SUM function I hade $goals = $row['sum']; and then would echo $goal in a div.

Aasim Azam Over a year ago

$q = mysql_query("SELECT COUNT(*) FROM cristiano WHERE season=12 AND which='club';") or die(mysql_error()); $row = mysql_fetch_assoc($q); $games = $row['count'];

Andolasoft Inc · Accepted Answer · 2014-03-07 10:37:26Z

0

I think you use the count() function it will work.Please find the below query SELECT COUNT(*) FROM table WHERE season='12' AND type='club';

answered Mar 7, 2014 at 10:37

Andolasoft Inc

1,2961 gold badge7 silver badges16 bronze badges

Comments

concept104 · Accepted Answer · 2014-03-07 10:47:14Z

0

here Table name is 'table' and type-column name is 'type' - These both words 'table' and 'type' are reserved words, so while executing the query , write the query as:

SELECT COUNT(*) FROM `table` WHERE `season`=12 AND `type`='club';

use ` sign as used in above code-line.

answered Mar 7, 2014 at 10:47

concept104

891 gold badge1 silver badge7 bronze badges

Collectives™ on Stack Overflow

How to count rows in MYSQL table with multiple column criteria?

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related