Post reply

Average Sold Per Month Query

  • August 18, 2005 at 8:55 am

    #583102

    Come on, that's over 2 weeks ago on a thread I didn't participate in!!! How am I supposed to remember that??

  • August 18, 2005 at 9:10 am

    #583112

    Actually, these queries aren't returning what i'm looking for.  Can i put it in English to explain my need ? :

    I want to return the average monthly qty sold by product for each product.

    The queries above return the product multi-times by either year or month

    ie.

    PRODA    2004    34

    PRODA    2005    45

    PRODB    2004     23

    PRODC   2003     23

    PRODC    2004   45

    PRODC    2005   56

    What i need is an average monthly qty sold by product 'over the years'

    ie:

    PRODA     23

    PRODB   45

    PRODC   45

    ...being:  product, average sold on a monthly basis

    ???

     

  • August 18, 2005 at 9:13 am

    #583116

    Noel is right Query should be

    SELECT Product, SUM(SaleQty)/12.0 AvgSalesPerMonth

    FROM

     orderdetail

    GROUP BY  Product

    Corrected in next post.

    Regards,
    gova

  • August 18, 2005 at 9:14 am

    #583117

    See Noeld's solution :

    SELECT Product, SUM(SaleQty) / NumberOfMonths AvgSales

    FROM

    dbo.orderdetail

    GROUP BY Product

    I'd probabely use DateDiff(M, getDate(), 'static date when the system was brough online)'

  • August 18, 2005 at 9:17 am

    #583122

    SELECT Product, SUM(SaleQty)/COUNT(DISTINCT CONVERT(VARCHAR, DATEPART(MONTH, SaleDate)) + CONVERT(VARCHAR, DATEPART(YEAR, SaleDate))) AvgSalesPerMonth

    FROM

     orderdetail

    GROUP BY  Product

    Will be more accurate

    DateDiff Remi posted is better since if there are no sales on a month that is also considered in that.

    Regards,
    gova

  • August 18, 2005 at 9:26 am

    #583128

    SELECT Product, 1.0*SUM(SaleQty)/datediff(m,Min(SaleDate)-Max(SaleDate))AvgSalesPerMonth

    FROM

     orderdetail

    GROUP BY  Product

    Will be simpler

     


    * Noel

  • August 18, 2005 at 9:29 am

    #583129

    SELECT Product, 1.0*SUM(SaleQty)/datediff(m,Min(SaleDate), Max(SaleDate))AvgSalesPerMonth

    FROM

    orderdetail

    GROUP BY Product

    Will be simpler correct

    Damn are we having a bad day or what??????

  • August 18, 2005 at 9:33 am

    #583130

    Damn are we having a bad day or what??????

    It's hard to beat your typing...  no time to loose

     


    * Noel

  • August 18, 2005 at 9:41 am

    #583139

    This was exactly what i wanted - cheers govinn

  • August 18, 2005 at 9:49 am

    #583144

    Check out my version of Noelds' code... might run a little faster and it is easier to understand.

  • August 18, 2005 at 9:49 am

    #583143

    That'll never change .

  • August 18, 2005 at 9:53 am

    #583150

    I could improve my typing skills ... which could give me an edge ?

     


    * Noel

  • August 18, 2005 at 9:56 am

    #583155

    I have a double edge, I think fast and type faster. Now you may have more experience but that doesn't atomatically translates in speed .

  • August 19, 2005 at 2:17 am

    #583427

    You're right Remi,

    I see what you are doing and it is a tad more sensible.

    .... No offence intended Noeld - actually thought what you suggested was also quite clever - "there's many ways to skin a cat"

    Thanks all

  • August 19, 2005 at 6:37 am

    #583483

    I just corrected his typo... he deserves the credit this time.

Viewing 15 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply