Hey guys, iam having major problems with this query:

"average percent increase in price of work between initial sale price and last sale price"?


if any one can find a solution it would be GREATLY appreciated.


CREATE TABLE [dbo].[Sales](
[SalesID] [int] IDENTITY(1,1) NOT NULL,
[LocationID] [smallint] NOT NULL,
[VendorID] [smallint] NOT NULL,
[WorkID] [int] NOT NULL,
[Sale_Status] [char](8)
[Previous_Sale_Price] [money] NULL,
[Sale_Price] [money] NULL,
[Commission] [float] NULL,
[Date_Up_For_Sales] [datetime] NULL,
[Date_Withdrawn_From_Sale] [datetime] NULL,
[Sale_Date] [datetime] NULL,
CONSTRAINT [pk_Sales] PRIMARY KEY CLUSTERED


CREATE TABLE [dbo].[Work](
[WorkID] [int] IDENTITY(1,1) NOT NULL,
[Work_TypeID] [tinyint] NOT NULL,
[Work_Title] [varchar](40) NOT NULL,
[Creation_Date] [datetime] NOT NULL,
CONSTRAINT [pk_Work] PRIMARY KEY CLUSTERED

not enough info, even for a homework assignment

surely the assignment gives a hint about what constitutes the initial and the final sale price? how do these relate to the columns Sale_Price and Previous_Sale_Price? what is Sale_Status and how does it apply?

I always find it amusing that these students think an "SQL Guru" is required to help them cheat on their simple homework assignments.

items are put up for sale, these items can be sold many times, if an item is sold or put up for sale it is marked accordingly in the sale status for eg "for sale" "sold" etc...

i need to no the average increase in price for an item between its original sale date and its last sale date.

So i assume the query will go something like this: filter by status to find all the items that have been involved in a sale, "sold" items, and then find first date it was put up for sale "Min" date_up_for_sale: then find its last sale date "max" sale_date: the average Previous_Sale_Price and Sale_Price

Use a common table expression to find the first price for an item, a second CTE for the last price, then calculate the average over the difference of both prices. Instead of CTEs, you can use subselects. No big deal...

if you want the average price.. then presumably you want to look at the AVERAGE of all sales, how you calculate that is up to you.. do you want a simple AVG or a weighted one.. where you give weight to number of items sold at that price
say you had 2 sales one for 10 items at 15.25, one at 14.00, one at 14.25
a simple average would be (15.25+14.00+14.25)/3= 14.50, whereas the weighted average would be (15.25*10 + 14 + 14.25)/12 =15.06

BTW have you investigated what functions are available as part of SQL?
I wonder if SQL has an AVG or even AVERAGE function that may help?

SQL has an AVG() aggregate function (aka column function), of course.