Consider the following:
CREATE TABLE test
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,Addresser_ID INT NOT NULL
,Destination_Country VARCHAR(12) NOT NULL);
INSERT INTO test (addresser_id,destination_country) VALUES
(1,'England'),
(2,'Spain'),
(1,'Denmark'),
(1,'Portugal'),
(2,'Spain'),
(3,'England'),
(3,'India');
SELECT * FROM test;
+----+--------------+---------------------+
| id | Addresser_ID | Destination_Country |
+----+--------------+---------------------+
| 1 | 1 | England |
| 2 | 2 | Spain |
| 3 | 1 | Denmark |
| 4 | 1 | Portugal |
| 5 | 2 | Spain |
| 6 | 3 | England |
| 7 | 3 | India |
+----+--------------+---------------------+
So, let's examine your query...
SELECT addresser_id,COUNT(*) total FROM test GROUP BY addresser_id;
+--------------+-------+
| addresser_id | total |
+--------------+-------+
| 1 | 3 |
| 2 | 2 |
| 3 | 2 |
+--------------+-------+
Clearly this isn't right, because although addresser '2' has two entries, they're both for the same country.
So, how about this...
SELECT addresser_id,COUNT(DISTINCT destination_country) total FROM test GROUP BY addresser_id;
+--------------+-------+
| addresser_id | total |
+--------------+-------+
| 1 | 3 |
| 2 | 1 |
| 3 | 2 |
+--------------+-------+
Closer. Now addresser '2' has a score of 1, but we still cannot see which countries have been addressed.
To do that we instead need something like this...
SELECT DISTINCT x.* FROM test x JOIN test y ON y.addresser_id = x.addresser_id AND y.destination_country <> x.destination_country;
+----+--------------+---------------------+
| id | Addresser_ID | Destination_Country |
+----+--------------+---------------------+
| 3 | 1 | Denmark |
| 4 | 1 | Portugal |
| 1 | 1 | England |
| 7 | 3 | India |
| 6 | 3 | England |
+----+--------------+---------------------+
--
Frequent responses
A. Read the Stickies at the top of the forum. Sometimes they disappear, in which case...
If your question concerns tables and data:
Within [ code ][ /code ] tags (leave off the spaces)...
1. Provide CREATE TABLE statements for each of the relevant tables
2. Provide a small but representative dataset for each of the tables, as a set of INSERT statements
3. Provide the resultset you'd expect from your query.
4. Provide the result of SELECT VERSION();
B. Group-wise Max queries
One frequently asked question relates to finding the Top-N of an aggregated result, one of a set of so-called 'GROUP-WISE MAX' queries.
Various solutions to this problem are provided at the following links:
dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html
www.artfulsoftware.com/infotree/queries.php?&bw=1179#101
jan.kneschke.de/projects/mysql/groupwise-max
C. Pivot Tables
A second frequently asked question involves the transposition of rows to columns, often involving the aggregation of data, so-called 'Pivot Table' queries. Although my preferred solution is to handle the transposition at the application level, e.g. with a bit of PHP, there is excellent discussion on this topic at www.artfulsoftware.com/infotree/queries.php?&bw=1179#523.
D. Hierarchical Data
A third frequently asked question relates to handling recursion in connection with hierarchical data.
Choices include, but are not limited to, the following:
- Join the table to itself as many times as could possibly be required
- Handle the recursion at the application level, e.g. with a PHP loop <-- my preference
- Use a Stored Procedure to handle the recursion
- Switch to a Nested Set (or some sort of hybrid model) instead of the 'so-called' Adjacency List model
All of these solutions are discussed more thoroughly elsewhere, including a number of excellent, popular, and hence easily googled, articles on Hierarchical data and MySQL.