Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2005
    Posts
    76

    Unanswered: Totals for this year AND last year

    I want to write a query which returns count values sold of each product for last three months... which I can write...the hard bit is, I also want to return what the count value was last year for this month

    i.e.
    Code:
                                Feb              Jan          Dec
                          2007   2006      2007 2006   2007 2006
    Soap                  2         9         1      2        9    9 
    Kitchen Towel      1         34       3      34       23    45
    Washing Up Liquid 33       3         5        6      7      33
    How can I write a query which will show count totals for both years??????
    Thanks
    Last edited by Pat Phelan; 02-25-08 at 11:48. Reason: Added VBulletin markers

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    WHERE  Month(myDateField) IN (1, 2, 3)
    GROUP
        BY Month(myDateField)
         , Year(myDateField)
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    write two separate queries and then LEFT OUTER JOIN them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    george: "last three months, also last year for this month"

    not jan feb mar for all years lumped together
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    It was this bit that lefd me to think otherwise
    Quote Originally Posted by STUCK1234
    Feb Jan Dec
    2007 2006 2007 2006 2007 2006
    I.e. Feb08, Feb07, Jan08, Jan07, Dec07, Dec06

    The question was confusatory; I took what I could from it
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I would use something like:
    Code:
    --  ptp  20080225  See http://www.dbforums.com/showthread.php?t=1627700
    
    IF Object_ID('t1627700', 'U') IS NOT NULL DROP TABLE t1627700
    
    CREATE TABLE t1627700 (
       foo		VARCHAR(20)
    ,  bar		INT
    ,  bat		DATETIME
       )
    
    INSERT INTO t1627700 (
       bat, bar, foo
       ) SELECT	'2008-02-01',	2,	'Soap'
       UNION SELECT	'2007-02-01',	9,	'Soap'
       UNION SELECT	'2008-01-01',	1,	'Soap'
       UNION SELECT	'2007-01-01',	2,	'Soap'
       UNION SELECT	'2007-12-01',	9,	'Soap'
       UNION SELECT	'2006-12-01',	9,	'Soap'
       UNION SELECT	'2008-02-01',	1,	'Kitchen Towel'
       UNION SELECT	'2007-02-01',	34,	'Kitchen Towel'
       UNION SELECT	'2008-01-01',	3,	'Kitchen Towel'
       UNION SELECT	'2007-01-01',	34,	'Kitchen Towel'
       UNION SELECT	'2007-12-01',	23,	'Kitchen Towel'
       UNION SELECT	'2006-12-01',	45,	'Kitchen Towel'
       UNION SELECT	'2008-02-01',	33,	'Washing Up Liquid'
       UNION SELECT	'2007-02-01',	3,	'Washing Up Liquid'
       UNION SELECT	'2008-01-01',	5,	'Washing Up Liquid'
       UNION SELECT	'2007-01-01',	6,	'Washing Up Liquid'
       UNION SELECT	'2007-12-01',	7,	'Washing Up Liquid'
       UNION SELECT	'2006-12-01',	33,	'Washing Up Liquid'
    
    SELECT
       foo AS [ ]
    ,  Sum(CASE WHEN bat = DateAdd(Month, DateDiff(month, '1753-01-01', GetDate()) -  0, '1753-01-01') THEN bar END)
    ,  Sum(CASE WHEN bat = DateAdd(Month, DateDiff(month, '1753-01-01', GetDate()) - 12, '1753-01-01') THEN bar END)
    ,  Sum(CASE WHEN bat = DateAdd(Month, DateDiff(month, '1753-01-01', GetDate()) -  1, '1753-01-01') THEN bar END)
    ,  Sum(CASE WHEN bat = DateAdd(Month, DateDiff(month, '1753-01-01', GetDate()) - 13, '1753-01-01') THEN bar END)
    ,  Sum(CASE WHEN bat = DateAdd(Month, DateDiff(month, '1753-01-01', GetDate()) -  2, '1753-01-01') THEN bar END)
    ,  Sum(CASE WHEN bat = DateAdd(Month, DateDiff(month, '1753-01-01', GetDate()) - 14, '1753-01-01') THEN bar END)
       FROM t1627700
       GROUP BY foo
    -PatP

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    pat, would your "solution" require any adjustment if it were to be run in july?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Fresh data?

    -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
  •