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

Question

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

Mureinik · Accepted Answer · 2016-12-29 14:43:46Z

1

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.

answered Dec 29, 2016 at 14:43

Mureinik

316k54 gold badges402 silver badges406 bronze badges

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

Comments

Raymond Nijland · Accepted Answer · 2016-12-29 15:15:40Z

0

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")

answered Dec 29, 2016 at 15:15

Raymond Nijland

11.6k2 gold badges26 silver badges37 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related