Re: getting sum of units sold in preceeding weeks

From: Nikhil (anonymous_at_discussions.microsoft.com)
Date: 05/12/04

  • Next message: Hugo Kornelis: "Re: Want to Know Colunm exist or not" 
    Date: Wed, 12 May 2004 00:41:09 -0700

    hi there...thanks for the help so far....

    wosh..i finally got it.....the query does give me week wise sale...for all items for the past 12 weeks..the way i wanted....but now in addition to this..i want two more columns...

    1. average
    2. standard deviation

    for each item...i want the average of all 12 weeks and the standard deviation of all 12 weeks in two adjoining columns...

    pray tell me how do i go about with this....

    regards

    Nikhil
         
         ----- Vishal Parkar wrote: -----
         
         
    >>can i have the same in diff columns...e.g. the table column heading would
         be
         
         yes possible, you only have to add group by clause. See following example
         
         --sample data
         
         create table tableA
         (dt_column datetime,
         sold_units int,
         itemid int)
         
         insert into tableA values(getdate() ,3, 1)
         insert into tableA values(getdate() -1 ,3, 1)
         insert into tableA values(getdate() -15,3, 1)
         insert into tableA values(getdate() ,3, 2)
         insert into tableA values(getdate() -15, 43, 2)
         
         --required query
         
         select itemid,sum(case when dt_column >= dateadd(dd, -7, {fn
         current_date()} )
         and dt_column <= getdate() then sold_units else 0 end) 'week1',
           sum(case when dt_column >= dateadd(dd, -15, {fn current_date()} ) and
         dt_column < dateadd(dd, -7, {fn current_date()} ) then sold_units else 0
         end) 'week2'
           from
           tableA
         group by itemid
         
         --
         Vishal Parkar
         vgparkar@yahoo.co.in | vgparkar@hotmail.com
         
         
         
         
         

  • Next message: Hugo Kornelis: "Re: Want to Know Colunm exist or not"

    Relevant Pages

    • Re: Simple Question about the SQL programming
      ... SELECT * FROM TableA WHERE NOT EXISTS ... SELECT * FROM TableB WHERE TableA.Subject =TableB.Subject ... > I am a newer in SQL programming .. ... > Best Regards ...
      (microsoft.public.sqlserver.server)
    • Re: please help
      ... Regards ... > SELECT * FROM TableA WHERE NOT EXISTS ... >> I need to run a query to get all the items out of a table where they are ...
      (microsoft.public.sqlserver.server)
    • Re: getting sum of units sold in preceeding weeks
      ... you only have to add group by clause. ... (dt_column datetime, ... insert into tableA values,3, 1) ...
      (microsoft.public.sqlserver.mseq)