Query To Rank Products

  • Thread starter Thread starter carl
  • Start date Start date
C

carl

My data looks as so:

day product qtysold
2-Mar A 1
2-Mar B 5
2-Mar A 4
2-Mar A 2
3-Mar A 2
3-Mar C 2
3-Mar B 5
3-Mar B 1
3-Mar D 2
3-Mar D 6


Is it possible for a query to take as input a product (eg A, B, or C) and
produce a result that shows the rank (based on qty sold) of the product
against all other products for each day that the product was sold ?

For example.

Qry for product A

day rank
2-Mar 1
3-Mar 3

or

Qry for product D

day rank
3-Mar 1


Thank you in advance.
 
Rankng per day?



SELECT day,
product,
qtySold,
(SELECT COUNT(*)
FROM table AS b
WHERE b.day=a.day
AND b.product = a.product
AND b.qtySold >= a.qtySold)
FROM table AS a




Vanderghast, Access MVP
 
ooooops, try this one instead


SELECT day,
product,
qtySold,
(SELECT COUNT(*)
FROM table AS b
WHERE b.day=a.day
AND b.qtySold >= a.qtySold)
FROM table AS a




The previous one was having one contraint in excess.



Vanderghast, Access MVP
 
Thanks.

It not quite getting what I need.

For each day the product was sold, I need the query to give me the rank of
that product relative to all (sum of all) products sold on that day.

If it is easier, the query could ask me to input the product and the result
whould be the ranking of that product for each day it was sold.

Does that make sense ?
 
SELECT COUNT(*)
FROM table AS a
WHERE day=[Enter the day]
AND product=[Enter product]
AND qtySold >=(SELECT MAX( b.qtySold )
FROM table AS b
WHERE b.day=a.day
AND b.product =a.product)



should do.


Vanderghast, Access MVP
 
Again, too fast, try:

SELECT COUNT(*)
FROM table AS a
WHERE day=[Enter the day]
AND qtySold <=(SELECT MAX( b.qtySold )
FROM table AS b
WHERE b.day=[Enter the day]
AND b.product =[Enter Product])



Vanderghast, Access MVP
 
TotalSales --
SELECT day, product, Sum([qtysold]) as SumOfqtysold
FROM YourTable
GROUP BY day, product;

SELECT Q.[day], Q.[product], Q.[SumOfqtysold], (SELECT COUNT(*) FROM
[TotalSales] Q1
WHERE Q1.[day] = Q.[day]
AND Q1.[product] = Q.[product]
AND Q1.[SumOfqtysold] >= Q.[SumOfqtysold])+1 AS Rank
FROM TotalSales AS Q
ORDER BY Q.[day], Q.[product], Q.[SumOfqtysold] DESC;
 
Hi. Thank you for your help. I almost gave up on this analysis.

When I run the query, it asks me to provide Q1.product and Q.Product. I
input the product name and the query returns multiple ranks for a given day
(for the product I entered earlier.

I was hoping that the result would show me the rank of the product against
all other products for each day that the product was sold.

I am probably not explaing this too well. Also, not sure if it matters but
my datbase has tens of thousands of records.

Would appreciate your continued assistance.

Regards.

Carl

KARL DEWEY said:
TotalSales --
SELECT day, product, Sum([qtysold]) as SumOfqtysold
FROM YourTable
GROUP BY day, product;

SELECT Q.[day], Q.[product], Q.[SumOfqtysold], (SELECT COUNT(*) FROM
[TotalSales] Q1
WHERE Q1.[day] = Q.[day]
AND Q1.[product] = Q.[product]
AND Q1.[SumOfqtysold] >= Q.[SumOfqtysold])+1 AS Rank
FROM TotalSales AS Q
ORDER BY Q.[day], Q.[product], Q.[SumOfqtysold] DESC;


--
KARL DEWEY
Build a little - Test a little


carl said:
Hi Karl.

I do not understand how to change the query to do what I need. Could you
assist ?
Click to expand...
Click to expand...
 
Does the first query run correctly? If not copy and post back. Product may
not be being output.
If first query is ok, copy and post back the second query.

carl said:
Hi. Thank you for your help. I almost gave up on this analysis.

When I run the query, it asks me to provide Q1.product and Q.Product. I
input the product name and the query returns multiple ranks for a given day
(for the product I entered earlier.

I was hoping that the result would show me the rank of the product against
all other products for each day that the product was sold.

I am probably not explaing this too well. Also, not sure if it matters but
my datbase has tens of thousands of records.

Would appreciate your continued assistance.

Regards.

Carl

KARL DEWEY said:
TotalSales --
SELECT day, product, Sum([qtysold]) as SumOfqtysold
FROM YourTable
GROUP BY day, product;

SELECT Q.[day], Q.[product], Q.[SumOfqtysold], (SELECT COUNT(*) FROM
[TotalSales] Q1
WHERE Q1.[day] = Q.[day]
AND Q1.[product] = Q.[product]
AND Q1.[SumOfqtysold] >= Q.[SumOfqtysold])+1 AS Rank
FROM TotalSales AS Q
ORDER BY Q.[day], Q.[product], Q.[SumOfqtysold] DESC;


--
KARL DEWEY
Build a little - Test a little


carl said:
Hi Karl.

I do not understand how to change the query to do what I need. Could you
assist ?

:

I woud say use Michel Walsh first one but put a totals query ahead of it.
--
KARL DEWEY
Build a little - Test a little


:

Thanks.

It not quite getting what I need.

For each day the product was sold, I need the query to give me the rank of
that product relative to all (sum of all) products sold on that day.

If it is easier, the query could ask me to input the product and the result
whould be the ranking of that product for each day it was sold.

Does that make sense ?

:

ooooops, try this one instead


SELECT day,
product,
qtySold,
(SELECT COUNT(*)
FROM table AS b
WHERE b.day=a.day
AND b.qtySold >= a.qtySold)
FROM table AS a




The previous one was having one contraint in excess.



Vanderghast, Access MVP


Rankng per day?



SELECT day,
product,
qtySold,
(SELECT COUNT(*)
FROM table AS b
WHERE b.day=a.day
AND b.product = a.product
AND b.qtySold >= a.qtySold)
FROM table AS a




Vanderghast, Access MVP




My data looks as so:

day product qtysold
2-Mar A 1
2-Mar B 5
2-Mar A 4
2-Mar A 2
3-Mar A 2
3-Mar C 2
3-Mar B 5
3-Mar B 1
3-Mar D 2
3-Mar D 6


Is it possible for a query to take as input a product (eg A, B, or C) and
produce a result that shows the rank (based on qty sold) of the product
against all other products for each day that the product was sold ?

For example.

Qry for product A

day rank
2-Mar 1
3-Mar 3

or

Qry for product D

day rank
3-Mar 1


Thank you in advance.
Click to expand...
Click to expand...
Click to expand...
 
Thank you for ypur help.

The first query ran correctly.

Here's the 2nd query:

SELECT Q.day, Q.product, Q.SumOfqtysold, (SELECT COUNT(*) FROM [TotalSales]
Q1 WHERE Q1.[day] = Q.[day] AND Q1.[product] = Q.[product]
AND Q1.[SumOfqtysold] >= Q.[SumOfqtysold])+1 AS Rank
FROM TotalSales AS Q
ORDER BY Q.day, Q.product, Q.SumOfqtysold DESC

Thank you again.



KARL DEWEY said:
Does the first query run correctly? If not copy and post back. Product may
not be being output.
If first query is ok, copy and post back the second query.

carl said:
Hi. Thank you for your help. I almost gave up on this analysis.

When I run the query, it asks me to provide Q1.product and Q.Product. I
input the product name and the query returns multiple ranks for a given day
(for the product I entered earlier.

I was hoping that the result would show me the rank of the product against
all other products for each day that the product was sold.

I am probably not explaing this too well. Also, not sure if it matters but
my datbase has tens of thousands of records.

Would appreciate your continued assistance.

Regards.

Carl

KARL DEWEY said:
TotalSales --
SELECT day, product, Sum([qtysold]) as SumOfqtysold
FROM YourTable
GROUP BY day, product;

SELECT Q.[day], Q.[product], Q.[SumOfqtysold], (SELECT COUNT(*) FROM
[TotalSales] Q1
WHERE Q1.[day] = Q.[day]
AND Q1.[product] = Q.[product]
AND Q1.[SumOfqtysold] >= Q.[SumOfqtysold])+1 AS Rank
FROM TotalSales AS Q
ORDER BY Q.[day], Q.[product], Q.[SumOfqtysold] DESC;


--
KARL DEWEY
Build a little - Test a little


:

Hi Karl.

I do not understand how to change the query to do what I need. Could you
assist ?

:

I woud say use Michel Walsh first one but put a totals query ahead of it.
--
KARL DEWEY
Build a little - Test a little


:

Thanks.

It not quite getting what I need.

For each day the product was sold, I need the query to give me the rank of
that product relative to all (sum of all) products sold on that day.

If it is easier, the query could ask me to input the product and the result
whould be the ranking of that product for each day it was sold.

Does that make sense ?

:

ooooops, try this one instead


SELECT day,
product,
qtySold,
(SELECT COUNT(*)
FROM table AS b
WHERE b.day=a.day
AND b.qtySold >= a.qtySold)
FROM table AS a




The previous one was having one contraint in excess.



Vanderghast, Access MVP


Rankng per day?



SELECT day,
product,
qtySold,
(SELECT COUNT(*)
FROM table AS b
WHERE b.day=a.day
AND b.product = a.product
AND b.qtySold >= a.qtySold)
FROM table AS a




Vanderghast, Access MVP




My data looks as so:

day product qtysold
2-Mar A 1
2-Mar B 5
2-Mar A 4
2-Mar A 2
3-Mar A 2
3-Mar C 2
3-Mar B 5
3-Mar B 1
3-Mar D 2
3-Mar D 6


Is it possible for a query to take as input a product (eg A, B, or C) and
produce a result that shows the rank (based on qty sold) of the product
against all other products for each day that the product was sold ?

For example.

Qry for product A

day rank
2-Mar 1
3-Mar 3

or

Qry for product D

day rank
3-Mar 1


Thank you in advance.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top