Meta Meta - 1 year ago 77
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 | | 555-555-5555
2 | | 666-666-6666
3 | | 555-555-5555
4 | | 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 | 555-555-5555 (2), 123-456-7890 (1) | 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 Source

Below query should work:

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

Here is SQL Fiddle.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download