0

How can I dynamically add a row count column to the following select statement based on results criteria - for example:

Query:

SELECT
    table1.name,
    table1.address,
    table1.job,
    table2.birthday
FROM
    table1
    INNER JOIN table2 ON table1.userid = table2.userid
WHERE
    table2.job = "Doctor"

Intended Results:

Intended Results Table

The dynamic count is counting the returned rows based on the birthday - if the birthday is different the count resets to 1 and counts.

If it is not possible in mySQL, please note I will be executing this statement in PDO, is there a way to add a dynamic count afterwards? Perhaps using PHP.

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • This is a rank within a group. If you have window functions, you can use the `RANK()` function. – Barmar Nov 03 '21 at 03:20
  • Can you please show how this can be done? I appreciate your help –  Nov 03 '21 at 03:22
  • In PHP, save the date in a variable, and increment a counter variable. If the current row's date is different from the variable, set the counter back to 1. – Barmar Nov 03 '21 at 03:22
  • Understood, Is there anyway to do this using only mySQL? –  Nov 03 '21 at 03:25
  • As I mentioned above, you can use the `RANK()` window function in MySQL 8.x. Before that you can use a user-defined variable. – Barmar Nov 03 '21 at 03:27

0 Answers0