Ranking based on two criteria

[accesshacker]

I am trying to perform ranking based on Total Sales and have been able to
accomplish it with the following SQL

SELECT T.Deal, T.SN, T.ST_TYPE, T.[LY Sales], (SELECT COUNT(*)
FROM ALLOC_TYPE_STORE T1
WHERE T1.[LY Sales] >= T.[LY Sales]) AS Rank
FROM ALLOC_TYPE_STORE AS T
ORDER BY T.[LY Sales] DESC;

I now need to be able to rank the sales based on their Store Type of A, B,
C, or D. There is a field in the ALLOC_TYPE_STORE table that contains the
data called ST_TYPE. I want to be able to show the ranking for each store
type starting with 1.

Any help will be greatly appreciated.

 

[Tom van Stiphout]

On Tue, 27 Oct 2009 17:15:01 -0700, accesshacker

You need a Totals query. Something similar to:
select StoreType, Sum(Sales)
from myTable
group by StoreType

-Tom.
Microsoft Access MVP

 

[KARL DEWEY]

UNTESTED UNTESTED
I assume [LY Sales] is field for sales value --
SELECT T.Deal, T.SN, T.ST_TYPE, Sum([LY Sales]) AS Total_Sales, (SELECT
Count(*)
FROM ALLOC_TYPE_STORE T1
WHERE T1.ST_TYPE = T.ST_TYPE AND Sum([T1].[LY Sales]) >= Sum([T].[LY
Sales])) AS Rank
FROM ALLOC_TYPE_STORE AS T
ORDER BY T.[ST_TYPE];

 

[accesshacker]

Thanks Karl. I modified the code you provided (see below) and it works. I
didn't need to sum on LY Sales though. Here is the modified code.

SELECT T.Deal, T.SN, T.ST_TYPE,T.[LY Sales], (SELECT COUNT(*)
FROM ALLOC_TYPE_STORE T1
WHERE T1.ST_TYPE = T.ST_TYPE and T1.[LY Sales] >= T.[LY Sales]) AS Rank
FROM ALLOC_TYPE_STORE AS T
ORDER BY T.ST_TYPE ASC, T.[LY Sales] DESC;

Thanks again for all the help, much appreciated!

UNTESTED UNTESTED
I assume [LY Sales] is field for sales value --
SELECT T.Deal, T.SN, T.ST_TYPE, Sum([LY Sales]) AS Total_Sales, (SELECT
Count(*)
FROM ALLOC_TYPE_STORE T1
WHERE T1.ST_TYPE = T.ST_TYPE AND Sum([T1].[LY Sales]) >= Sum([T].[LY
Sales])) AS Rank
FROM ALLOC_TYPE_STORE AS T
ORDER BY T.[ST_TYPE];

--
Build a little, test a little.

I am trying to perform ranking based on Total Sales and have been able to
accomplish it with the following SQL

SELECT T.Deal, T.SN, T.ST_TYPE, T.[LY Sales], (SELECT COUNT(*)
FROM ALLOC_TYPE_STORE T1
WHERE T1.[LY Sales] >= T.[LY Sales]) AS Rank
FROM ALLOC_TYPE_STORE AS T
ORDER BY T.[LY Sales] DESC;

I now need to be able to rank the sales based on their Store Type of A, B,
C, or D. There is a field in the ALLOC_TYPE_STORE table that contains the
data called ST_TYPE. I want to be able to show the ranking for each store
type starting with 1.

Any help will be greatly appreciated.