Statistical analysis - Top [N] most frequent values in column.

[Joost de Vries]

Hi,

I'm currently building a database in wich errors by employees are stored.
Each error has a 'type' colum (integer) and what I want to do now is create a
report in wich the top 10 error types can be seen.
Even though there are several threads on similar questions I'm unable to
apply the posted solutions to my database, mainly because there is no
grouping in this report.
Records will be selected on date, employee or check id.

To give you a better idea of the concept:

Table: errors
--------------------
id (int)
check (int)
employee (int)
type (int)
date (date)
comments (txt)

The output should be something like:

Top 10 errors (december '06)
Type 1 - 69 occurances
Type 2 - 45 occurances
Type 3 - 34 occurances
etc..

I hope you're able to help me out on this one..

Kind regards.

 

[Duane Hookom]

If you wanted the top 10 items sold in the Northwind OrderDetails table
(orders containing the product) your query would be:
SELECT TOP 10 [Order Details].ProductID,
Count([Order Details].OrderID) AS CountOfOrderID
FROM [Order Details]
GROUP BY [Order Details].ProductID
ORDER BY Count([Order Details].OrderID) DESC;