Grouping and Top N Counts

  • Thread starter Thread starter js
  • Start date Start date
J

js

I have a dataset with Name, Product, sales (for example).
I want a query to return the top 25 products per person.
So the query would group Name, product and do a count of
sales (top 25 descending).
The problem is, when I do this the results return the top
25 sales total, not top 25 products per Name.

How can I accomplish this?

thanks
 
Hi,


SELECT Name, Product
FROM somewhere As a
WHERE a.primaryKey IN ( SELECT TOP 25 b.primaryKey
FROM somewhere As b
WHERE b.name=a.name
ORDER BY b.ProductValue DESC, b.primaryKey )
ORDER BY Name, ProductValue DESC


The inner most query take the top 25 records (ordered by product value), for
a given name, a.name, and extract, from those records, their primarykey
value. The outer most query "loop" for each name, keep the records that
match something returned by the inner most query through the primary key,
and re-order the whole stuff.


Note that by default, Jet return the TOP N+equi, meaning that if there are
ex-equo in the breaking position, ie, for TOP 3 (ASCending values) in the
list ( 1, 2, 3, 3, 3, 3, 7, 8, 9), JET would return ( 1, 2, 3, 3, 3, 3) ,
ie, 6 values. Adding the primary key in the ORDER BY makes sure such
equi-ness won't occur and you will get only 25 records, at most. But if this
is unfair for a potential 26th which has the same value than the 25th,
remove the b.primaryKey in the order by clause or the innermost query:

SELECT Name, Product
FROM somewhere As a
WHERE a.primaryKey IN ( SELECT TOP 25 b.primaryKey
FROM somewhere As b
WHERE b.name=a.name
ORDER BY b.ProductValue DESC)
ORDER BY Name, ProductValue DESC




and you may get more than 25 records per name, but that comes from
equi-ness.



Hoping it may help,
Vanderghast, Access MVP
 
Hi,


It seems we would need an intermediate query, that will find the count:

SELECT physname, drugname, COUNT(*) As CountOf
FROM [claims data]
GROUP BY physname, drugname


say this is qcount.


Our main query is then

SELECT physname, drugname, countOf
FROM qcount As a
WHERE a.drugname IN (
SELECT TOP 25 b.drugname
FROM qcount As b
WHERE a.physname=b.physname
ORDER BY b.countOf DESC )
ORDER BY physname, countOf DESC




Hoping it may help,
Vanderghast, Access MVP


js said:
Thanks,
Let me get a little specific:
Table Names=Claims data and Physician Table
Goal: Group all physicians in Physician table and list TOP
25 Drugs (Drug Name) from the claims table by a count of
TCN.
Here is what I have but am getting stuck:

SELECT PhysName, DrugName
From [claims data] as a
where a.primarykey IN (select top 25 b.primaryKey
From [claims data] as b
Where b.physname=b.physname
Order By Count([Claims Data].TCN) DESC)
Order by PhysName, Count([Claims Data].TCN) DESC;

Any help is greatly appreciated!!
Thanks!
-----Original Message-----
Hi,


SELECT Name, Product
FROM somewhere As a
WHERE a.primaryKey IN ( SELECT TOP 25 b.primaryKey
FROM somewhere As b
WHERE b.name=a.name
ORDER BY b.ProductValue DESC, b.primaryKey )
ORDER BY Name, ProductValue DESC


The inner most query take the top 25 records (ordered by product value), for
a given name, a.name, and extract, from those records, their primarykey
value. The outer most query "loop" for each name, keep the records that
match something returned by the inner most query through the primary key,
and re-order the whole stuff.


Note that by default, Jet return the TOP N+equi, meaning that if there are
ex-equo in the breaking position, ie, for TOP 3 (ASCending values) in the
list ( 1, 2, 3, 3, 3, 3, 7, 8, 9), JET would return ( 1, 2, 3, 3, 3, 3) ,
ie, 6 values. Adding the primary key in the ORDER BY makes sure such
equi-ness won't occur and you will get only 25 records, at most. But if this
is unfair for a potential 26th which has the same value than the 25th,
remove the b.primaryKey in the order by clause or the innermost query:

SELECT Name, Product
FROM somewhere As a
WHERE a.primaryKey IN ( SELECT TOP 25 b.primaryKey
FROM somewhere As b
WHERE b.name=a.name
ORDER BY b.ProductValue DESC)
ORDER BY Name, ProductValue DESC




and you may get more than 25 records per name, but that comes from
equi-ness.



Hoping it may help,
Vanderghast, Access MVP






.
Click to expand...
Click to expand...
 
Back
Top