Hi,

I am not able to find the answer to the following query.

A table has three columns: Year, Quarter and Sales price.
Each year has four quarters and values for corresponding sales price
for each quarter is given. Populate the table and give the output as
five columns: year, Qrtr1, Qrtr2, Qrtr3, Qrtr4 and sales price.

There can be multiple prices in quarter but in the result set i
just want average price per quarter and per year respectively (I mean
Quarter1 column will contain average price for all quarter-1 entries
for a specific year, whereas the Year column will contain average price

for that whole year).

I am struggling with the "Qarter-wise sales" display part.

Thanks in advance!

On Oct 27, 1:28 pm, "Rose" <rose.washing... (AT) yahoo (DOT) com> wrote:
The solution may vary according to your DBMS which you failed to
mention.
You might look into Analytic functions if your DBMS supports them. This
is a case of a cross tab query. If that's not enough information. post
again.
Ed

Every year have 4 quarters. In another word fixed number of quarters.
So, following query will give your required results.

SELECT Year
, AVG(Sales_price) AS "Year_AVG"
, AVG(CASE
WHEN Quarter = 1 THEN
Sales_price
END) AS "Qrtr1"
, AVG(CASE
WHEN Quarter = 2 THEN
Sales_price
END) AS "Qrtr2"
, AVG(CASE
WHEN Quarter = 3 THEN
Sales_price
END) AS "Qrtr3"
, AVG(CASE
WHEN Quarter = 4 THEN
Sales_price
END) AS "Qrtr4"
FROM Price_Table
GROUP BY Year
ORDER BY Year
;

Thanks a lot Tonkuma!

Tonkuma wrote: