CODEWITHSUNDEEP
mysqlsqlselectcount
Mamun
Mamun

Reputation: 13

MySQL - Count rows with equal values but only if name occurs in other column

I have a table JT with the following columns:

Id  |  Name |  Date     
1   |  John |  2015-01-01
2   |  Alex |  2016-01-01
3   |  Pete |  2016-01-01
4   |  Alex |  2016-08-08    
5   |  John |  2016-08-08

I want to count the number of rows with the same date. I have done this by:

$query_21="SELECT Date, COUNT(*) AS total FROM JT GROUP BY Date";
$result_21=mysql_query($query_21);

while($row = mysql_fetch_array($result_21)) {

    echo $row['Date'] . ":";
    echo $row['total'] . "<br>";     
}

That works fine returning:

2015-01-01: 1

2016-01-01: 2

2016-08-08: 2

However, I have a variable $Name, and I want to make the call so a row is only inserted in the resulting array if $Name is included in one of the rows during the count. So if $Name = "John" it should return:

2015-01-01: 1

2016-08-08: 2

Upvotes: 1

Views: 1439

Answers (2)

Raymond Nijland
Raymond Nijland

Reputation: 11602

it's also possible to use Mureinik's query with SUM and without use of COUNT in combination with CASE statement. I think this query is more easy to read and understand because the query is more compact. 

SELECT 
   total.Date
 , COUNT(*) AS total
FROM 
  total 
GROUP BY
  total.Date
HAVING 
  SUM(total.Name = "John")

Upvotes: 0

Mureinik
Mureinik

Reputation: 311808

This can be done with a where clause using an exists condition, but since you're grouping anyway, I think using a having clause may be more elegant:

SELECT   Date, COUNT(*) AS total 
FROM     jt
GROUP BY Date
HAVING   (COUNT(CASE name WHEN $name THEN name END) > 0)

Note:
 $name should, of course, be a bound variable in a prepared statement.

Upvotes: 1

Related Questions