Results 1 to 7 of 7
  1. #1
    Join Date
    May 2007
    Posts
    2

    Unanswered: Calling SQL Guru's (query help needed!!)

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I always find it amusing that these students think an "SQL Guru" is required to help them cheat on their simple homework assignments.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    May 2007
    Posts
    2

    Try this?

    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

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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?
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    SQL has an AVG() aggregate function (aka column function), of course.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •