Category Group Ranking

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following SQL that ranks by YTDSALES. I would like to group rank
YTDSALES by Category (see illustration 2).

SQL:
SELECT a.ITEMNO, a. CAT, a.YTDSALES, (Select Count(*) from [DIV 1] Where
YTDSALES > a.YTDSALES;)+1 AS RANK
FROM [DIV 1] AS a
GROUP BY a.ITEMNO, a.CAT, a.YTDSALES
ORDER BY a.YTDSALES;

1 - Resolute of SQL
ITEMNO CAT SALES RANK
1869593 PICKLES/OLIVES/RELISH 673 1
1893148 SALAD TOPPINGS 581 2
1892744 SALAD TOPPINGS 360 3
1894393 SALAD TOPPINGS 259 4
1893890 SALAD TOPPINGS 203 5
1869346 PICKLES/OLIVES/RELISH 41 6
1869247 PICKLES/OLIVES/RELISH 39 7

2 - Would like it group ranked by CAT
ITEMNO CAT SALES RANK
1869593 PICKLES/OLIVES/RELISH 673 1
1869346 PICKLES/OLIVES/RELISH 41 2
1869247 PICKLES/OLIVES/RELISH 39 3
1893148 SALAD TOPPINGS 581 1
1892744 SALAD TOPPINGS 360 2
1894393 SALAD TOPPINGS 259 3
1893890 SALAD TOPPINGS 203 4
 
AFSSkier said:
I have the following SQL that ranks by YTDSALES. I would like to group rank
YTDSALES by Category (see illustration 2).

SQL:
SELECT a.ITEMNO, a. CAT, a.YTDSALES, (Select Count(*) from [DIV 1] Where
YTDSALES > a.YTDSALES;)+1 AS RANK
FROM [DIV 1] AS a
GROUP BY a.ITEMNO, a.CAT, a.YTDSALES
ORDER BY a.YTDSALES;

1 - Resolute of SQL
ITEMNO CAT SALES RANK
1869593 PICKLES/OLIVES/RELISH 673 1
1893148 SALAD TOPPINGS 581 2
1892744 SALAD TOPPINGS 360 3
1894393 SALAD TOPPINGS 259 4
1893890 SALAD TOPPINGS 203 5
1869346 PICKLES/OLIVES/RELISH 41 6
1869247 PICKLES/OLIVES/RELISH 39 7

2 - Would like it group ranked by CAT
ITEMNO CAT SALES RANK
1869593 PICKLES/OLIVES/RELISH 673 1
1869346 PICKLES/OLIVES/RELISH 41 2
1869247 PICKLES/OLIVES/RELISH 39 3
1893148 SALAD TOPPINGS 581 1
1892744 SALAD TOPPINGS 360 2
1894393 SALAD TOPPINGS 259 3
1893890 SALAD TOPPINGS 203 4
Click to expand...

This seems to work for your example:

SELECT ITEMNO, CAT, YTDSALES, (Select Count(*) from [DIV 1] AS a Where
[DIV 1].YTDSALES < a.YTDSALES AND a.CAT = [DIV 1].CAT)+1 AS
RANKWITHINCAT FROM [DIV 1]
GROUP BY ITEMNO, CAT, YTDSALES
ORDER BY CAT, YTDSALES DESC;

James A. Fortune
(e-mail address removed)
 
Thanks!

James A. Fortune said:
AFSSkier said:
I have the following SQL that ranks by YTDSALES. I would like to group rank
YTDSALES by Category (see illustration 2).

SQL:
SELECT a.ITEMNO, a. CAT, a.YTDSALES, (Select Count(*) from [DIV 1] Where
YTDSALES > a.YTDSALES;)+1 AS RANK
FROM [DIV 1] AS a
GROUP BY a.ITEMNO, a.CAT, a.YTDSALES
ORDER BY a.YTDSALES;

1 - Resolute of SQL
ITEMNO CAT SALES RANK
1869593 PICKLES/OLIVES/RELISH 673 1
1893148 SALAD TOPPINGS 581 2
1892744 SALAD TOPPINGS 360 3
1894393 SALAD TOPPINGS 259 4
1893890 SALAD TOPPINGS 203 5
1869346 PICKLES/OLIVES/RELISH 41 6
1869247 PICKLES/OLIVES/RELISH 39 7

2 - Would like it group ranked by CAT
ITEMNO CAT SALES RANK
1869593 PICKLES/OLIVES/RELISH 673 1
1869346 PICKLES/OLIVES/RELISH 41 2
1869247 PICKLES/OLIVES/RELISH 39 3
1893148 SALAD TOPPINGS 581 1
1892744 SALAD TOPPINGS 360 2
1894393 SALAD TOPPINGS 259 3
1893890 SALAD TOPPINGS 203 4
Click to expand...

This seems to work for your example:

SELECT ITEMNO, CAT, YTDSALES, (Select Count(*) from [DIV 1] AS a Where
[DIV 1].YTDSALES < a.YTDSALES AND a.CAT = [DIV 1].CAT)+1 AS
RANKWITHINCAT FROM [DIV 1]
GROUP BY ITEMNO, CAT, YTDSALES
ORDER BY CAT, YTDSALES DESC;

James A. Fortune
(e-mail address removed)
Click to expand...
 
Try this ---
SELECT a.ITEMNO, a. CAT, a.YTDSALES, (Select Count(*) from [DIV 1] Where
a.CAT = CAT AND YTDSALES > a.YTDSALES;)+1 AS RANK FROM [DIV 1] AS a
GROUP BY a.ITEMNO, a.CAT, a.YTDSALES
ORDER BY a.CAT. a.YTDSALES;
 
I have a report from your suggested query & I get the following Error 3612.
Please help. The query runs fine, just not the report.

"Multi-level GROUP BY clause is not allowed in a subquery. (Error 3612)
An SQL subquery cannot list more than one field in a GROUP BY clause."

--
Thanks, Kevin


James A. Fortune said:
AFSSkier said:
I have the following SQL that ranks by YTDSALES. I would like to group rank
YTDSALES by Category (see illustration 2).

SQL:
SELECT a.ITEMNO, a. CAT, a.YTDSALES, (Select Count(*) from [DIV 1] Where
YTDSALES > a.YTDSALES;)+1 AS RANK
FROM [DIV 1] AS a
GROUP BY a.ITEMNO, a.CAT, a.YTDSALES
ORDER BY a.YTDSALES;

1 - Resolute of SQL
ITEMNO CAT SALES RANK
1869593 PICKLES/OLIVES/RELISH 673 1
1893148 SALAD TOPPINGS 581 2
1892744 SALAD TOPPINGS 360 3
1894393 SALAD TOPPINGS 259 4
1893890 SALAD TOPPINGS 203 5
1869346 PICKLES/OLIVES/RELISH 41 6
1869247 PICKLES/OLIVES/RELISH 39 7

2 - Would like it group ranked by CAT
ITEMNO CAT SALES RANK
1869593 PICKLES/OLIVES/RELISH 673 1
1869346 PICKLES/OLIVES/RELISH 41 2
1869247 PICKLES/OLIVES/RELISH 39 3
1893148 SALAD TOPPINGS 581 1
1892744 SALAD TOPPINGS 360 2
1894393 SALAD TOPPINGS 259 3
1893890 SALAD TOPPINGS 203 4
Click to expand...

This seems to work for your example:

SELECT ITEMNO, CAT, YTDSALES, (Select Count(*) from [DIV 1] AS a Where
[DIV 1].YTDSALES < a.YTDSALES AND a.CAT = [DIV 1].CAT)+1 AS
RANKWITHINCAT FROM [DIV 1]
GROUP BY ITEMNO, CAT, YTDSALES
ORDER BY CAT, YTDSALES DESC;

James A. Fortune
(e-mail address removed)
Click to expand...
 
I resolved my error by creating a MakeTake from the Ranking Query. Then
joined tblRank to DIV1 table for the report.
--
Thanks, Kevin


James A. Fortune said:
AFSSkier said:
I have a report from your suggested query & I get the following Error 3612.
Please help. The query runs fine, just not the report.

"Multi-level GROUP BY clause is not allowed in a subquery. (Error 3612)
An SQL subquery cannot list more than one field in a GROUP BY clause."
Click to expand...

Try it without the GROUP BY part. Use the report's Sorting and Grouping
to display groups. If the ITEMNO values are unique (they should be if
the [Div 1] table was created to summarize information) then the GROUP
BY part doesn't do anything anyway.

James A. Fortune
(e-mail address removed)
Click to expand...
 
AFSSkier said:
I have a report from your suggested query & I get the following Error 3612.
Please help. The query runs fine, just not the report.

"Multi-level GROUP BY clause is not allowed in a subquery. (Error 3612)
An SQL subquery cannot list more than one field in a GROUP BY clause."
Click to expand...

Try it without the GROUP BY part. Use the report's Sorting and Grouping
to display groups. If the ITEMNO values are unique (they should be if
the [Div 1] table was created to summarize information) then the GROUP
BY part doesn't do anything anyway.

James A. Fortune
(e-mail address removed)
 
Back
Top