Query To Rank Products

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.
 
M

Michel Walsh

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
 
M

Michel Walsh

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
 
C

carl

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 ?
 
M

Michel Walsh

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
 
M

Michel Walsh

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
 
C

carl

Hi Karl.

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

KARL DEWEY

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;
 
C

carl

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...
 
K

KARL DEWEY

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...
 
C

carl

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Add a sequence number(question number) to a paragraph which has a question and 4 options and answer 0
Ranking Formula 6
Adding sequence numbers to paragraphs 0
Strange Cumulative in Query Results 2
Column to Rows 1
Calcuate date difference when two dates are same 1
Query SELECT in SELECT? 2
Concatenate Values in Crosstab Query 5

Top