Query Help... Group By Issue

  • Hi,

    I am asked to make a report where the user can view the number of items they have bought daily, in the last six months in a specific price range.

    Example:

    They want to see how many things were ordered by customer id '12' inthe past six month daily and in the price ranges (1-500, 501-1000, 1001-1500, 1501-2000)

    The table structure is following

    CID (decimal)

    Amount (decimal)

    Item ID (decimal)

    processdate (datetime)

    I did write the query for one price range but dont know how it can be done for all of them, so far ive done this but copy pasting the data is taking me ages because there are days that they have not ordered anything and then i have to manually feed in the data in excel for the days they did order something..

    select count (*) as [1001to1500], processdate as [Date]

    from RPTView

    where CID= '12'

    AND processdate between '08/01/07' and '02/01/08'

    and amount between 2001.00 and 2500.00

    Group by processdate

    order by processdate asc

    and this gives me the number of itesm broken down into dates for the price range 1001-1500, please let me know what i can do to get the results for all ranges together.. someone recommeded to use the excel pivot tables so thats what im working on right now...

    Thanks for any help

  • Does this help?

    Select ProcessDate

    ,[1to500] = count(case when amount between 1 and 500 then amount else null end)

    ,[501to1000] = count(case when amount between 501 and 1000 then amount else null end)

    ...

    From

    RPTView

    where CID= '12'

    AND processdate between '08/01/07' and '02/01/08'

    Group by processdate

    order by processdate asc

    This is off the top of my head with no testing so...

    If you want customer in there as well, then get rid of the customer id filter, and add it into the select and group by

    Hope this helps.

  • This worked!!.. Thank you so much, saved me a couple of hours, I am new to SQL so learned something very helpful today.. thanks alot

Viewing 3 posts - 1 through 2 (of 2 total)

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