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


Problem :

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?



Solution :

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();
   }

Mysql Tutorials

Mysql Howto..

MYSQL JOIN. How to join only first match? (Join limit 1)

How to manage an order_by column for dynamic ordering, mysql and php

How to get the total from 2 column in a week

How to manage one to one relationship in mySql?

How to resolve MySQL error “You can't specify target table X for update in FROM clause”? [duplicate]

How to create a single foreign key that points to a multiple-column primary key?

How to debug correctly an Insert statement with PDO (mysql)?

Trying to figure out how to write an SQL query to return the nonintersection of two tables for a mysql database, having trouble

How to get lowest value for 2 duplicate entries in PHP and MySQL

How to insert records in loop in MySQL based on results from another table

How to set a specific rule in Rsyslogd for an IP address and send to a mysql server

How to use Hibernate HQL and AVG-Function in MySQL

How to get all selected ids at once ? PHP/MySQL question

How to output MySQL table in PHP with prepared statements

how to link tables together using timestamp sql, mysql

How to set token in mysql and let the token work only in one hour?

MySQL How to `SUM` a value on a many-to-one relationship, in the case that there are multiple values take only 1 the Maximum?

How to use mysql to find a field name of a record?

How can I inform sqlalchemy to raise an error whenever MySQL throws a warning?

How does sorting on enums actually work?

How to add a LEFT JOIN to this MySQL Query?

How to get list of tables, that don`t have a specific column in MySQL

How to migrate from Oracle RDB for OpenVMS to MySQL in Windows?

How to include MySQL database schema on GitHub?

jQuery/PHP Hide Div, Update Information from MySQL, Show Div New Information

How to export a data frame in R to a table in MySQL

How to display dates between to given values along with another column in MySQL

Getting Root & Leaf Nodes for a Given Id - How to?

How to check if mysql queries succeeded

How do you deal with the fact that default parameters are not possible for Mysql SPs?