Counting multiple entries in a MySQL database?

Question

Hi all,

I'm trying to count multiple entries in a MySQL database, I know how to use COUNT(), but the exact syntax I want to get the results I need eludes me.

The problem: Table structure: ID, CODE, AUTHOR, COUNTRY, TIMESTAMP.

Code, Author and Country overlap many times in the table. I am trying to discover if there is one simple query that can be ran to return (using WHERE clause on COUNTRY) the author field, the code field, and then a final field that counts the number of times the CODE was present in the query result.

So, theoretically I could end up with an array like:

array('author', 'code', 'codeAppearsNTimes');

Authors also have varying codes associated with them, so I don't want the results merged. I suppose the end result would be: 'This author is associated with this code this many times'.

Is this possible with MySQL?

Thanks in advance.

Answer 1

SELECT author, code, COUNT(*)
FROM   table
WHERE  country = @country
GROUP BY
       author, code