Creating Mathematical Formulas and Calculations

I've created a sql statement that retrieves number data from various table joins. The number data is then grouped according to various categories. What I need to do is to calculate the average of all the number data in a particular group. How do i go about this? Once calculated, the average needs to be displayed under the data.

For example, the report will list say five numbers (some sets may have more, it depends on how much data is returned based on the query), then under the five numbers, the average is given

2

4

3

6

0

Average: 5

What technique is best? Do I have to calculate the numbers in sql or do I need to configure the report to calculate the average? If so, how? Can someone show me step by step how to do averages for a set of data in the reporting services?

I am completely new to doing reports, I'm just a hobbyist, and I've only used databases to retrieve basic data, but not make manipulations for reports.

Any help will be appreciated.

You can use the "Avg" function in reporting services to do this calculation. Average returns the average of the numbers.

Creating Deciles

Hello,

I want to set up a query in SQL Server that is "grouped by" a variable
PRICE. Since PRICE takes on continuous decimal values, I want to
create deciles based on this variable and then display the average
price in each decile. As background, PRICE is a calculated quantity:
I divide a table field SLS_DLRS by a field SLS_UNTS to get it. I also
want to include an average SLS_UNTS for each decile.

So essentially, I want the result to be something like:
DECILE AVG_PRICE AVG_SLS_UNTS
1 0.50 5.2
2 1.50 4.7
... ... ...
10 9.50 1.1

Is there a way to do this in an SQL statement?

Thanks in advance,
Indraneel"Indraneel Sheorey" <indraneel.sheorey@.dartmouth.edu> wrote in message
news:3556ef5.0311141040.1e38d2a8@.posting.google.co m...
> Hello,
> I want to set up a query in SQL Server that is "grouped by" a variable
> PRICE. Since PRICE takes on continuous decimal values, I want to
> create deciles based on this variable and then display the average
> price in each decile. As background, PRICE is a calculated quantity:
> I divide a table field SLS_DLRS by a field SLS_UNTS to get it. I also
> want to include an average SLS_UNTS for each decile.
> So essentially, I want the result to be something like:
> DECILE AVG_PRICE AVG_SLS_UNTS
> 1 0.50 5.2
> 2 1.50 4.7
> ... ... ...
> 10 9.50 1.1
> Is there a way to do this in an SQL statement?
> Thanks in advance,
> Indraneel

There are different methods, with varying degrees of accuracy, to
calculate percentiles. Here's one reasonable method described through
an example. Consider the 50th percentile (5th decile or median) for the
following numbers, arranged in increasing order:

3, 5, 7, 8, 9, 11, 13, 15

1. Compute the rank R of the 50th percentile.
R = P / 100 * (N + 1)
P = the desired percentile
N = number of numbers in the collection

R = 50 / 100 * (8 + 1) = 9 / 2 = 4.5
2. When R is an integer, the Pth percentile would be the number with
rank R.
3. When R is not an integer, as in this case, the Pth percentile is
computed by interpolation as follows:
a. Define IR as the integer portion of R. For this example, IR is 4.
b. Define FR as the fractional portion of R. For this example, FR is .5.
c. Find the values in the sequence with rank IR and IR + 1. For example,
this means the value with rank 4 and the value with rank 5, which
are 8 and 9, respectively.
d. Interpolate by multiplying the difference between the scores by FR
and adding the result to the lower score. For this example, this is
.5 * (9 - 8) + 8 = 8.5

Therefore, the 50th percentile is 8.5.

CREATE TABLE T
(
sls_dlrs DECIMAL (8, 2) NOT NULL CHECK (sls_dlrs > 0),
sls_unts INT NOT NULL CHECK (sls_unts > 0)
)

CREATE VIEW TPrices (sls_dlrs, sls_unts, price)
AS
SELECT sls_dlrs, sls_unts, sls_dlrs / sls_unts
FROM T

-- Rank prices in increasing order. For a given price P in an ordered collection,
-- give both the rank of the first occurrence of P and the rank of the last
-- occurrence of P. The first price P in an ordered collection has rank 1.
CREATE VIEW IncreasingOrderRanks (price, rank_first, rank_last)
AS
SELECT P1.price,
COUNT(*) - P1.occurrences + 1,
COUNT(*)
FROM (SELECT price, COUNT(*) AS occurrences
FROM TPrices
GROUP BY price) AS P1
INNER JOIN
TPrices AS P2
ON P2.price <= P1.price
GROUP BY P1.price, P1.occurrences

CREATE TABLE Digits
(
d INT NOT NULL PRIMARY KEY CHECK (d BETWEEN 0 AND 9)
)

INSERT INTO Digits (d)
VALUES (0)
INSERT INTO Digits (d)
VALUES (1)
INSERT INTO Digits (d)
VALUES (2)
INSERT INTO Digits (d)
VALUES (3)
INSERT INTO Digits (d)
VALUES (4)
INSERT INTO Digits (d)
VALUES (5)
INSERT INTO Digits (d)
VALUES (6)
INSERT INTO Digits (d)
VALUES (7)
INSERT INTO Digits (d)
VALUES (8)
INSERT INTO Digits (d)
VALUES (9)

-- Need to generate natural numbers up to 100 to represent whole number
-- percentile ranks
CREATE VIEW PercentileRanks (rank)
AS
SELECT Ones.d + 10 * Tens.d + 1
FROM Digits AS Ones
CROSS JOIN
Digits AS Tens

CREATE VIEW Percentiles (percentile, rank)
AS
SELECT CASE WHEN PR.rank * (RN.rank + 1) / 100.0 < R1.rank
THEN R1.price
WHEN PR.rank * (RN.rank + 1) / 100.0 > RN.rank
THEN RN.price
WHEN PR.rank * (RN.rank + 1) / 100.0 >
CAST(PR.rank * (RN.rank + 1) / 100.0 AS INT)
THEN
(SELECT CASE WHEN R1.price = R2.price
THEN R1.price
ELSE (PR.rank * (RN.rank + 1) / 100.0 -
CAST(PR.rank * (RN.rank + 1) / 100.0 AS INT)) *
(R2.price - R1.price) + R1.price
END
FROM IncreasingOrderRanks AS R1
INNER JOIN
IncreasingOrderRanks AS R2
ON CAST(PR.rank * (RN.rank + 1) / 100.0 AS INT)
BETWEEN R1.rank_first AND R1.rank_last AND
CAST(PR.rank * (RN.rank + 1) / 100.0 AS INT) + 1
BETWEEN R2.rank_first AND R2.rank_last)
ELSE (SELECT price
FROM IncreasingOrderRanks
WHERE CAST(PR.rank * (RN.rank + 1) / 100.0 AS INT)
BETWEEN rank_first AND rank_last)
END,
PR.rank
FROM PercentileRanks AS PR
CROSS JOIN
(SELECT 1, price
FROM IncreasingOrderRanks
WHERE rank_first = 1) AS R1(rank, price)
CROSS JOIN
(SELECT MAX(rank_last), MAX(price)
FROM IncreasingOrderRanks) AS RN(rank, price)

CREATE VIEW Deciles (decile, rank)
AS
SELECT percentile, rank / 10
FROM Percentiles
WHERE rank % 10 = 0

-- The desired query
SELECT D1.rank, AVG(P.price) AS avg_price, AVG(P.sls_unts) AS avg_sls_unts
FROM Deciles AS D1
LEFT OUTER JOIN
Deciles AS D2
ON D2.rank = D1.rank - 1
INNER JOIN
TPrices AS P
ON P.price <= D1.decile AND
(D2.decile IS NULL OR P.price > D2.decile)
GROUP BY D1.rank
ORDER BY D1.rank

Regards,
jag|||"John Gilson" <jag@.acm.org> wrote in message news:<REktb.175923$pT1.22236@.twister.nyc.rr.com>...
> "Indraneel Sheorey" <indraneel.sheorey@.dartmouth.edu> wrote in message
> news:3556ef5.0311141040.1e38d2a8@.posting.google.co m...
> > Hello,
> > I want to set up a query in SQL Server that is "grouped by" a variable
> > PRICE. Since PRICE takes on continuous decimal values, I want to
> > create deciles based on this variable and then display the average
> > price in each decile. As background, PRICE is a calculated quantity:
> > I divide a table field SLS_DLRS by a field SLS_UNTS to get it. I also
> > want to include an average SLS_UNTS for each decile.
> > So essentially, I want the result to be something like:
> > DECILE AVG_PRICE AVG_SLS_UNTS
> > 1 0.50 5.2
> > 2 1.50 4.7
> > ... ... ...
> > 10 9.50 1.1
> > Is there a way to do this in an SQL statement?
> > Thanks in advance,
> > Indraneel
> There are different methods, with varying degrees of accuracy, to
> calculate percentiles. Here's one reasonable method described through
> an example. Consider the 50th percentile (5th decile or median) for the
> following numbers, arranged in increasing order:
> 3, 5, 7, 8, 9, 11, 13, 15
> 1. Compute the rank R of the 50th percentile.
> R = P / 100 * (N + 1)
> P = the desired percentile
> N = number of numbers in the collection
> R = 50 / 100 * (8 + 1) = 9 / 2 = 4.5
> 2. When R is an integer, the Pth percentile would be the number with
> rank R.
> 3. When R is not an integer, as in this case, the Pth percentile is
> computed by interpolation as follows:
> a. Define IR as the integer portion of R. For this example, IR is 4.
> b. Define FR as the fractional portion of R. For this example, FR is .5.
> c. Find the values in the sequence with rank IR and IR + 1. For example,
> this means the value with rank 4 and the value with rank 5, which
> are 8 and 9, respectively.
> d. Interpolate by multiplying the difference between the scores by FR
> and adding the result to the lower score. For this example, this is
> .5 * (9 - 8) + 8 = 8.5
> Therefore, the 50th percentile is 8.5.
> CREATE TABLE T
> (
> sls_dlrs DECIMAL (8, 2) NOT NULL CHECK (sls_dlrs > 0),
> sls_unts INT NOT NULL CHECK (sls_unts > 0)
> )
> CREATE VIEW TPrices (sls_dlrs, sls_unts, price)
> AS
> SELECT sls_dlrs, sls_unts, sls_dlrs / sls_unts
> FROM T
> -- Rank prices in increasing order. For a given price P in an ordered collection,
> -- give both the rank of the first occurrence of P and the rank of the last
> -- occurrence of P. The first price P in an ordered collection has rank 1.
> CREATE VIEW IncreasingOrderRanks (price, rank_first, rank_last)
> AS
> SELECT P1.price,
> COUNT(*) - P1.occurrences + 1,
> COUNT(*)
> FROM (SELECT price, COUNT(*) AS occurrences
> FROM TPrices
> GROUP BY price) AS P1
> INNER JOIN
> TPrices AS P2
> ON P2.price <= P1.price
> GROUP BY P1.price, P1.occurrences
> CREATE TABLE Digits
> (
> d INT NOT NULL PRIMARY KEY CHECK (d BETWEEN 0 AND 9)
> )
> INSERT INTO Digits (d)
> VALUES (0)
> INSERT INTO Digits (d)
> VALUES (1)
> INSERT INTO Digits (d)
> VALUES (2)
> INSERT INTO Digits (d)
> VALUES (3)
> INSERT INTO Digits (d)
> VALUES (4)
> INSERT INTO Digits (d)
> VALUES (5)
> INSERT INTO Digits (d)
> VALUES (6)
> INSERT INTO Digits (d)
> VALUES (7)
> INSERT INTO Digits (d)
> VALUES (8)
> INSERT INTO Digits (d)
> VALUES (9)
> -- Need to generate natural numbers up to 100 to represent whole number
> -- percentile ranks
> CREATE VIEW PercentileRanks (rank)
> AS
> SELECT Ones.d + 10 * Tens.d + 1
> FROM Digits AS Ones
> CROSS JOIN
> Digits AS Tens
> CREATE VIEW Percentiles (percentile, rank)
> AS
> SELECT CASE WHEN PR.rank * (RN.rank + 1) / 100.0 < R1.rank
> THEN R1.price
> WHEN PR.rank * (RN.rank + 1) / 100.0 > RN.rank
> THEN RN.price
> WHEN PR.rank * (RN.rank + 1) / 100.0 >
> CAST(PR.rank * (RN.rank + 1) / 100.0 AS INT)
> THEN
> (SELECT CASE WHEN R1.price = R2.price
> THEN R1.price
> ELSE (PR.rank * (RN.rank + 1) / 100.0 -
> CAST(PR.rank * (RN.rank + 1) / 100.0 AS INT)) *
> (R2.price - R1.price) + R1.price
> END
> FROM IncreasingOrderRanks AS R1
> INNER JOIN
> IncreasingOrderRanks AS R2
> ON CAST(PR.rank * (RN.rank + 1) / 100.0 AS INT)
> BETWEEN R1.rank_first AND R1.rank_last AND
> CAST(PR.rank * (RN.rank + 1) / 100.0 AS INT) + 1
> BETWEEN R2.rank_first AND R2.rank_last)
> ELSE (SELECT price
> FROM IncreasingOrderRanks
> WHERE CAST(PR.rank * (RN.rank + 1) / 100.0 AS INT)
> BETWEEN rank_first AND rank_last)
> END,
> PR.rank
> FROM PercentileRanks AS PR
> CROSS JOIN
> (SELECT 1, price
> FROM IncreasingOrderRanks
> WHERE rank_first = 1) AS R1(rank, price)
> CROSS JOIN
> (SELECT MAX(rank_last), MAX(price)
> FROM IncreasingOrderRanks) AS RN(rank, price)
> CREATE VIEW Deciles (decile, rank)
> AS
> SELECT percentile, rank / 10
> FROM Percentiles
> WHERE rank % 10 = 0
> -- The desired query
> SELECT D1.rank, AVG(P.price) AS avg_price, AVG(P.sls_unts) AS avg_sls_unts
> FROM Deciles AS D1
> LEFT OUTER JOIN
> Deciles AS D2
> ON D2.rank = D1.rank - 1
> INNER JOIN
> TPrices AS P
> ON P.price <= D1.decile AND
> (D2.decile IS NULL OR P.price > D2.decile)
> GROUP BY D1.rank
> ORDER BY D1.rank
> Regards,
> jag

Thanks for your detailed and comprehensive answer, jag. I am having
trouble creating the Percentiles view, however. I am running SQL
Server locally on a desktop computer, so I think this command may be
too much for it -- I get timeout messages. Is there a way I can split
up this command to create the Percentiles view?

Thanks again,
Indraneel|||"Indraneel Sheorey" <indraneel.sheorey@.dartmouth.edu> wrote in message
news:3556ef5.0311151130.73f293e4@.posting.google.co m...
> "John Gilson" <jag@.acm.org> wrote in message news:<REktb.175923$pT1.22236@.twister.nyc.rr.com>...
> > "Indraneel Sheorey" <indraneel.sheorey@.dartmouth.edu> wrote in message
> > news:3556ef5.0311141040.1e38d2a8@.posting.google.co m...
> > > Hello,
> > > > I want to set up a query in SQL Server that is "grouped by" a variable
> > > PRICE. Since PRICE takes on continuous decimal values, I want to
> > > create deciles based on this variable and then display the average
> > > price in each decile. As background, PRICE is a calculated quantity:
> > > I divide a table field SLS_DLRS by a field SLS_UNTS to get it. I also
> > > want to include an average SLS_UNTS for each decile.
> > > > So essentially, I want the result to be something like:
> > > DECILE AVG_PRICE AVG_SLS_UNTS
> > > 1 0.50 5.2
> > > 2 1.50 4.7
> > > ... ... ...
> > > 10 9.50 1.1
> > > > Is there a way to do this in an SQL statement?
> > > > Thanks in advance,
> > > Indraneel
> > There are different methods, with varying degrees of accuracy, to
> > calculate percentiles. Here's one reasonable method described through
> > an example. Consider the 50th percentile (5th decile or median) for the
> > following numbers, arranged in increasing order:
> > 3, 5, 7, 8, 9, 11, 13, 15
> > 1. Compute the rank R of the 50th percentile.
> > R = P / 100 * (N + 1)
> > P = the desired percentile
> > N = number of numbers in the collection
> > R = 50 / 100 * (8 + 1) = 9 / 2 = 4.5
> > 2. When R is an integer, the Pth percentile would be the number with
> > rank R.
> > 3. When R is not an integer, as in this case, the Pth percentile is
> > computed by interpolation as follows:
> > a. Define IR as the integer portion of R. For this example, IR is 4.
> > b. Define FR as the fractional portion of R. For this example, FR is .5.
> > c. Find the values in the sequence with rank IR and IR + 1. For example,
> > this means the value with rank 4 and the value with rank 5, which
> > are 8 and 9, respectively.
> > d. Interpolate by multiplying the difference between the scores by FR
> > and adding the result to the lower score. For this example, this is
> > .5 * (9 - 8) + 8 = 8.5
> > Therefore, the 50th percentile is 8.5.
> > CREATE TABLE T
> > (
> > sls_dlrs DECIMAL (8, 2) NOT NULL CHECK (sls_dlrs > 0),
> > sls_unts INT NOT NULL CHECK (sls_unts > 0)
> > )
> > CREATE VIEW TPrices (sls_dlrs, sls_unts, price)
> > AS
> > SELECT sls_dlrs, sls_unts, sls_dlrs / sls_unts
> > FROM T
> > -- Rank prices in increasing order. For a given price P in an ordered collection,
> > -- give both the rank of the first occurrence of P and the rank of the last
> > -- occurrence of P. The first price P in an ordered collection has rank 1.
> > CREATE VIEW IncreasingOrderRanks (price, rank_first, rank_last)
> > AS
> > SELECT P1.price,
> > COUNT(*) - P1.occurrences + 1,
> > COUNT(*)
> > FROM (SELECT price, COUNT(*) AS occurrences
> > FROM TPrices
> > GROUP BY price) AS P1
> > INNER JOIN
> > TPrices AS P2
> > ON P2.price <= P1.price
> > GROUP BY P1.price, P1.occurrences
> > CREATE TABLE Digits
> > (
> > d INT NOT NULL PRIMARY KEY CHECK (d BETWEEN 0 AND 9)
> > )
> > INSERT INTO Digits (d)
> > VALUES (0)
> > INSERT INTO Digits (d)
> > VALUES (1)
> > INSERT INTO Digits (d)
> > VALUES (2)
> > INSERT INTO Digits (d)
> > VALUES (3)
> > INSERT INTO Digits (d)
> > VALUES (4)
> > INSERT INTO Digits (d)
> > VALUES (5)
> > INSERT INTO Digits (d)
> > VALUES (6)
> > INSERT INTO Digits (d)
> > VALUES (7)
> > INSERT INTO Digits (d)
> > VALUES (8)
> > INSERT INTO Digits (d)
> > VALUES (9)
> > -- Need to generate natural numbers up to 100 to represent whole number
> > -- percentile ranks
> > CREATE VIEW PercentileRanks (rank)
> > AS
> > SELECT Ones.d + 10 * Tens.d + 1
> > FROM Digits AS Ones
> > CROSS JOIN
> > Digits AS Tens
> > CREATE VIEW Percentiles (percentile, rank)
> > AS
> > SELECT CASE WHEN PR.rank * (RN.rank + 1) / 100.0 < R1.rank
> > THEN R1.price
> > WHEN PR.rank * (RN.rank + 1) / 100.0 > RN.rank
> > THEN RN.price
> > WHEN PR.rank * (RN.rank + 1) / 100.0 >
> > CAST(PR.rank * (RN.rank + 1) / 100.0 AS INT)
> > THEN
> > (SELECT CASE WHEN R1.price = R2.price
> > THEN R1.price
> > ELSE (PR.rank * (RN.rank + 1) / 100.0 -
> > CAST(PR.rank * (RN.rank + 1) / 100.0 AS INT))
*
> > (R2.price - R1.price) + R1.price
> > END
> > FROM IncreasingOrderRanks AS R1
> > INNER JOIN
> > IncreasingOrderRanks AS R2
> > ON CAST(PR.rank * (RN.rank + 1) / 100.0 AS INT)
> > BETWEEN R1.rank_first AND R1.rank_last AND
> > CAST(PR.rank * (RN.rank + 1) / 100.0 AS INT) + 1
> > BETWEEN R2.rank_first AND R2.rank_last)
> > ELSE (SELECT price
> > FROM IncreasingOrderRanks
> > WHERE CAST(PR.rank * (RN.rank + 1) / 100.0 AS INT)
> > BETWEEN rank_first AND rank_last)
> > END,
> > PR.rank
> > FROM PercentileRanks AS PR
> > CROSS JOIN
> > (SELECT 1, price
> > FROM IncreasingOrderRanks
> > WHERE rank_first = 1) AS R1(rank, price)
> > CROSS JOIN
> > (SELECT MAX(rank_last), MAX(price)
> > FROM IncreasingOrderRanks) AS RN(rank, price)
> > CREATE VIEW Deciles (decile, rank)
> > AS
> > SELECT percentile, rank / 10
> > FROM Percentiles
> > WHERE rank % 10 = 0
> > -- The desired query
> > SELECT D1.rank, AVG(P.price) AS avg_price, AVG(P.sls_unts) AS avg_sls_unts
> > FROM Deciles AS D1
> > LEFT OUTER JOIN
> > Deciles AS D2
> > ON D2.rank = D1.rank - 1
> > INNER JOIN
> > TPrices AS P
> > ON P.price <= D1.decile AND
> > (D2.decile IS NULL OR P.price > D2.decile)
> > GROUP BY D1.rank
> > ORDER BY D1.rank
> > Regards,
> > jag
> Thanks for your detailed and comprehensive answer, jag. I am having
> trouble creating the Percentiles view, however. I am running SQL
> Server locally on a desktop computer, so I think this command may be
> too much for it -- I get timeout messages. Is there a way I can split
> up this command to create the Percentiles view?
> Thanks again,
> Indraneel

Some appropriate storing and indexing of intermediate results should help.
Apply the following revisions to the above code.

-- *** ADD ***
CREATE TABLE TPricesBag
(
sls_dlrs DECIMAL (8, 2) NOT NULL,
sls_unts INT NOT NULL,
price DECIMAL (8, 2) NOT NULL
)

-- *** ADD ***
INSERT INTO TPricesBag (sls_dlrs, sls_unts, price)
SELECT sls_dlrs, sls_unts, price
FROM TPrices

-- *** ADD ***
CREATE TABLE TPricesSet
(
price DECIMAL (8, 2) NOT NULL,
occurrences INT NOT NULL,
PRIMARY KEY (price)
)

-- *** ADD ***
INSERT INTO TPricesSet (price, occurrences)
SELECT price, COUNT(*)
FROM TPricesBag
GROUP BY price

-- *** REPLACE ***
-- Rank prices in increasing order. For a given price P in an ordered collection,
-- give both the rank of the first occurrence of P and the rank of the last
-- occurrence of P. The first price P in an ordered collection has rank 1.
CREATE VIEW IncreasingOrderRanks (price, rank_first, rank_last)
AS
SELECT P1.price,
COUNT(*) - P1.occurrences + 1,
COUNT(*)
FROM TPricesSet AS P1
INNER JOIN
TPricesBag AS P2
ON P2.price <= P1.price
GROUP BY P1.price, P1.occurrences

-- *** ADD ***
CREATE TABLE Ranks
(
price DECIMAL (8, 2) NOT NULL,
rank_first INT NOT NULL CHECK (rank_first >= 1),
rank_last INT NOT NULL CHECK (rank_last >= 1),
CHECK (rank_last >= rank_first),
PRIMARY KEY (rank_first)
)

-- *** ADD ***
INSERT INTO Ranks (price, rank_first, rank_last)
SELECT price, rank_first, rank_last
FROM IncreasingOrderRanks

-- *** REPLACE ***
CREATE VIEW Percentiles (percentile, rank)
AS
SELECT CASE
WHEN PR.rank * (RN.rank + 1) / 100.0 < R1.rank
THEN R1.price
WHEN PR.rank * (RN.rank + 1) / 100.0 > RN.rank
THEN RN.price
WHEN PR.rank * (RN.rank + 1) / 100.0 >
CAST(PR.rank * (RN.rank + 1) / 100.0 AS INT)
THEN
(SELECT CASE WHEN R1.price = R2.price
THEN R1.price
ELSE (PR.rank * (RN.rank + 1) / 100.0 -
CAST(PR.rank * (RN.rank + 1) / 100.0 AS INT)) *
(R2.price - R1.price) + R1.price
END
FROM Ranks AS R1
INNER JOIN
Ranks AS R2
ON CAST(PR.rank * (RN.rank + 1) / 100.0 AS INT)
BETWEEN R1.rank_first AND R1.rank_last AND
CAST(PR.rank * (RN.rank + 1) / 100.0 AS INT) + 1
BETWEEN R2.rank_first AND R2.rank_last)
ELSE (SELECT price
FROM Ranks
WHERE CAST(PR.rank * (RN.rank + 1) / 100.0 AS INT)
BETWEEN rank_first AND rank_last)
END,
PR.rank
FROM PercentileRanks AS PR
CROSS JOIN
(SELECT 1, price
FROM Ranks
WHERE rank_first = 1) AS R1(rank, price)
CROSS JOIN
(SELECT MAX(rank_last), MAX(price)
FROM Ranks) AS RN(rank, price)

-- *** REPLACE ***
-- The desired query
SELECT D1.rank, AVG(P.price) AS avg_price,
AVG(P.sls_unts) AS avg_sls_unts
FROM Deciles AS D1
LEFT OUTER JOIN
Deciles AS D2
ON D2.rank = D1.rank - 1
INNER JOIN
TPricesBag AS P
ON P.price <= D1.decile AND
(D2.decile IS NULL OR P.price > D2.decile)
GROUP BY D1.rank
ORDER BY D1.rank

Given a table T with 10,000 rows (randomly generated data) on a 1.7 GHz
P4 with 500 MB RAM, the above query took a bit over 1 minute. Not
blazing, hopefully acceptable.

Regards,
jag