Count Unique (yep, again)

[sehmke]

Hello,

I noticed my subject has been discussed numerous times in this forum. I must have read nearly all topics, but I simply can't get it to work.

I have a table listing: country | category | article | turnover

I wish to make a query which gives me:

country | sum of turnover | count of (unique) categories used by this country | count of (unique) articles sold by this country.

This way I can evaluate with how many different categories a certain country achieves a certain turnover...

It look so simple but I can't figure it out. I hope I'm clear in my question.

HEEELP!

Cheers,
Stefan

 

[ajetrumpet]

Well, I have one question to ask you (I can't get it from your post)...

Are you wanting the SUM OF TURNOVER for each article, that is in each category, which in turn, would be in each country?? That sounds like unique records to me, which I think is what you want...??

If this is the case, I just wrote a FAQ on it, check that out HERE. I think though, that you're wanting to get other information from your query, like the number of articles written in a country. I would say just write two or three queries using the FAQ technique, and then UNION the queries together. Or you could JOIN them as well, probably wouldn't matter in this case...

If it's not that complicated, then just group by country and you probably can get what you want....

SELECT [country], sum(turnover] as [whatever], 
count([categories]) as [whatever2], count([articles]) as [whatever3]

FROM [table]
GROUP BY [country]

 

[gblack]

Try this...

I think the problem is trying to do each of these aggregations at the same time is that you will get many more records than you really want.

Another way to do this is to section each aggregation you want into separate queries.

Keep country as your Key field.

SO…

SELECT country | sum of turnover…

SELECT country | count of (unique) categories

SELECT country | count of (unique) articles sold

This should give you one line per country per each query.

Then make a query linking each of these queries by country…