From: Tim Thorburn
Date: June 14 2003 11:14pm
Subject: selecting DISTINCT and COUNT in MySQL
List-Archive: http://lists.mysql.com/mysql/142724
Message-Id: <5.2.1.1.0.20030614180800.00bc67e8@mail.nwconx.net>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"; format=flowed

Hi,

I'm setting up a small tracking program for a site I'm working on - 
basically it monitors all external web and email links that are clicked 
from our site and stores them into a MySQL database.

Now I'm trying to extract the information from the database and display it 
on screen with PHP.  I've made a query which reads:
SELECT DISTINCT trEmail FROM db-name;

This clearly selects all the distinct email addresses from my table, but I 
would like to put a count beside each of the distinct addresses to show how 
many times each link was clicked.

For example, I'd like it to look something like the following:

email address               # sent
fake@stripped              12

Meaning that the email addresses 'fake@stripped' was clicked a total of 12 
times.

I'm thinking there should be a way to do this with a single SQL query ... 
although after most of the day of trying and searching google - I've come 
up with nothing but a headache.

Any thoughts?

Thanks
-Tim