Meta Meta - 12 days ago 5
MySQL Question

MYSQL SELECT unique values from column where column2 = value & count how many times each unique value was found

I am looking to select and count each occurrence of column phone_number where column2 email_eddress == value.

Example DB Tables

Table: some_numbers

id | sender_email | phoner_number
1 | email@example.com | 555-555-5555
2 | email2@example.net | 666-666-6666
3 | email@example.com | 555-555-5555
4 | email@example.com | 123-456-7890


I am trying to select the phone_number value and the number of times each unique phone_number number was found WHERE sender_email = $email variable that is set to check an individual email address).

I can't seem to figure out how to query this in a way that I can just output something like shown below.

EXAMPLE OUTPUT

email@example.com | 555-555-5555 (2), 123-456-7890 (1)

email2@example.net | 666-666-6666 (1)

My Attempt:

$results = $db->query("SELECT DISTINCT phone_number FROM some_numbers GROUP BY sender_email");
foreach($results as $result){
$number = trim($result['phone_number']);

echo $number.BR;
}

Answer

Below query should work:

select sender_email, phone_number, count(*)
from some_numbers 
group by sender_email, phone_number;

Here is SQL Fiddle.