[RESOLVED] Grouping twice and a count!

[Jon12345]

I have the following fields in a table called logfile:

Date
Keyword (any text)
Position (a number from 1 to 10)

I want to create a list that groups by Date and then groups by Keyword.

e.g.

1/1/05 dogs
1/1/05 cats
2/1/05 dogs
2/1/05 cats
etc

But to the right of those first 2 columns, I would like to display 10 columns, labelled Position1, Position2...Position10

In each of those colums, I would like to show a count of the number of times Position. Let me explain...

e.g. lets assume you have the following records for day 1.

Date: 1/1/05
Keyword: Dog
Position: 2

Date: 1/1/05
Keyword: Dog
Position: 1

Date: 1/1/05
Keyword: Dog
Position: 2

Date: 1/1/05
Keyword: Dog
Position: 3

Date: 1/1/05
Keyword: Dog
Position: 2

I would like the output to be like this:

Date: 1/1/05
Keyword: Dog
Position1: 1
Position2: 3
Position3: 1
..,
Position10: 0

Hope that makes sense! Thats the principle.

In practice I would like to display the list in columns. They are basically summary figures of who clicked what advert on a website, broken down by keyword and date.

Anyone know how to do that with a MySQL query?

Thanks,

Jon

[Grisu]

I have named your table 'test'. This should give the results you are looking for:

SELECT Count(test.keyword) AS [Anzahl von keyword], test.date, test.keyword, test.position
FROM test
GROUP BY test.date, test.keyword, test.position;

Grisu

[Grisu]

sorry, I used Access, in MySQL it should look like this:

SELECT Count(test.keyword) AS keywordcount, test.date, test.keyword, test.position
FROM test
GROUP BY test.date, test.keyword, test.position;

Grisu

[Jon12345]

Thanks!