Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2003
    Location
    here
    Posts
    19

    Unanswered: Group by question

    salesrep dollars months
    a1 $1.00 4/1/2004
    a1 $2.00 5/2/2004
    a1 $3.00 5/3/2004
    a1 $4.00 6/4/2004
    a1 $5.00 6/5/2004
    a1 $6.00 6/6/2004
    b1 $7.00 3/7/2004
    b1 $8.00 3/8/2004
    b1 $9.00 4/9/2004
    b1 $10.00 5/10/2004
    b1 $11.00 5/11/2004
    b1 $12.00 6/12/2004
    b1 $13.00 6/13/2004

    Given the above data, I need to come up with a report that displays atotal by salesrep by month.

    Any help would be greatly appreciated. Getting a total by month is where I'm hung up.
    Program, that's what I do.

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Do you mean something like this?

    drop table test
    create table test(salem varchar(10),amount money, saledate datetime)
    insert test values('a',1,'10/1/2003')
    insert test values('a',1,'10/11/2003')
    insert test values('b',1,'1/11/2003')
    insert test values('c',1,'8/11/2003')

    select salem,datepart(yyyy,saledate) 'Year',datepart(mm,saledate) 'Month',sum(amount)
    from test
    group by salem,datepart(yyyy,saledate),datepart(mm,saledate )

  3. #3
    Join Date
    Jul 2003
    Location
    here
    Posts
    19
    snail - thanks for the reply.

    this is one step in the process. actually what i'm looking for is a way to create a cross-tabbed view of the resulting data... I've since figured out that I'll need to create a few more tables to get the desired results.
    Program, that's what I do.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just to send Brett and Blindman into fits, I'd try something like:
    Code:
    CREATE TABLE tToeJam (
       salesrep	VARCHAR(5)	NOT NULL
    ,  dollars	MONEY		NOT NULL
    ,  months	DATETIME	NOT NULL
       )
    
    INSERT INTO tToeJam (salesrep, dollars, months)
       SELECT       'a1', $1.00, '4/1/2004'
       UNION SELECT 'a1',  $2.00, '5/2/2004'
       UNION SELECT 'a1',  $3.00, '5/3/2004'
       UNION SELECT 'a1',  $4.00, '6/4/2004'
       UNION SELECT 'a1',  $5.00, '6/5/2004'
       UNION SELECT 'a1',  $6.00, '6/6/2004'
       UNION SELECT 'b1',  $7.00, '3/7/2004'
       UNION SELECT 'b1',  $8.00, '3/8/2004'
       UNION SELECT 'b1',  $9.00, '4/9/2004'
       UNION SELECT 'b1', $10.00, '5/10/2004'
       UNION SELECT 'b1', $11.00, '5/11/2004'
       UNION SELECT 'b1', $12.00, '6/12/2004'
       UNION SELECT 'b1', $13.00, '6/13/2004'
    
    DECLARE @cSQL		VARCHAR(2000)
    ,  @cRep		VARCHAR(5)
    
    SELECT @cSQL = 'SELECT Convert(CHAR(6), months, 112)'
    
    DECLARE zReps CURSOR FOR SELECT DISTINCT
       salesrep
       FROM tToeJam
       ORDER BY salesrep
    
    OPEN zReps
    FETCH zReps INTO @cRep
    WHILE 0 = @@fetch_status
       BEGIN
          SELECT @cSQL = @cSQL + ', Sum(CASE WHEN ''' + @cRep + ''' = salesrep THEN dollars END) AS ''' + @cRep + ''''
          FETCH zReps INTO @cRep
       END
    
    CLOSE zReps
    DEALLOCATE zReps
    
    SELECT @cSQL = @cSQL + ' FROM tToeJam GROUP BY Convert(CHAR(6), months, 112)'
    
    EXECUTE (@cSQL)
    
    DROP TABLE tToeJam
    -PatP

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Bluh. Spurgle-pop! Glubbity glubbity glubbity sneerp!

    P', pu', puh', puhblagablech!

    select salesrep,
    sum(case when month(months) = 1 then dollars else 0 end) as Jan,
    sum(case when month(months) = 2 then dollars else 0 end) as Feb,
    sum(case when month(months) = 3 then dollars else 0 end) as Mar,
    sum(case when month(months) = 4 then dollars else 0 end) as Apr,
    sum(case when month(months) = 5 then dollars else 0 end) as May,
    sum(case when month(months) = 6 then dollars else 0 end) as Jun,
    sum(case when month(months) = 7 then dollars else 0 end) as Jul,
    sum(case when month(months) = 8 then dollars else 0 end) as Aug,
    sum(case when month(months) = 9 then dollars else 0 end) as Sep,
    sum(case when month(months) = 10 then dollars else 0 end) as Oct,
    sum(case when month(months) = 11 then dollars else 0 end) as Nov,
    sum(case when month(months) = 12 then dollars else 0 end) as Dec
    from yourtable
    where year(month) = 2004
    group by salesrep

    ...aaahhhhh. Now I feel better....
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ohhhhhh! That was good! Definitely worth it!

    -PatP

Posting Permissions

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