List:General Discussion« Previous MessageNext Message »
From:Peter Lovatt Date:June 14 2003 11:14pm
Subject:RE: selecting DISTINCT and COUNT in MySQL
View as plain text  
Hi


    SELECT
    COUNT(DISTINCT trEmail ) as clicks
    , trEmail  FROM table
    GROUP BY trEmail
    ORDER BY clicks DESC

    will probably give what you are looking for


Peter

-----Original Message-----
From: Tim Thorburn [mailto:webmaster@stripped]
Sent: 15 June 2003 00:14
To: mysql@stripped
Subject: selecting DISTINCT and COUNT in MySQL


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



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1



Thread
selecting DISTINCT and COUNT in MySQLTim Thorburn15 Jun
  • RE: selecting DISTINCT and COUNT in MySQLPeter Lovatt15 Jun