Results 1 to 8 of 8
  1. #1
    Join Date
    May 2011
    Posts
    13

    Exclamation Unanswered: Stumped Missing months in SQL

    I have this query:

    Code:
    SELECT SUM(t1.totalprice) as totalprice,
    SUM(t1.sales) as salessubtotal, t1.`year` as `year`,
    t1.mnth as `month`
    from 
    (SELECT
    Sum(crm_sales.totalprice) AS totalprice,
    MONTHNAME(crm_sales.date) AS mnth,
    YEAR(crm_sales.date) AS `year`,
    Count(crm_sales.totalprice) AS sales,
    MONTH(crm_sales.date) AS `ordering`
    FROM `crm_sales`
    WHERE `date` BETWEEN '2009-01-01' AND '2010-05-28'
    GROUP BY crm_sales.date
    ) as t1
    GROUP BY t1.`year`,t1.mnth
    ORDER BY t1.`year`, t1.ordering
    TotalPrice Sales Year Month

    130567.41 635 2009 May
    139533.66 682 2009 June
    141245.63 681 2009 July
    121226.32 588 2009 August
    121582.47 588 2009 September
    134636.40 660 2009 October
    107815.05 524 2009 November
    106716.81 521 2009 December
    123851.60 609 2010 January
    126752.72 619 2010 February
    151362.32 732 2010 March
    124616.72 605 2010 April
    150702.71 736 2010 May

    The query returns the correct data but i need the missing months to be 0 for sales.

    ANy ideas

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    create a table with two columns -- year and month -- and populate it with rows for all the years/months that you want to see

    then do a LEFT OUTER JOIN to your query as a derived table (subquery in the FROM clause)

    vwalah!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2011
    Posts
    13

    Question Clarification request

    Thanks for the reply.

    WOuld the table be a temporary table?

    If a temp table is possible would I then need to use a stored procedure?

    WOuld it work with just the months asthe years are dynamic?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by kiwimediapro View Post
    WOuld the table be a temporary table?
    only if you want it to be

    Quote Originally Posted by kiwimediapro View Post
    If a temp table is possible would I then need to use a stored procedure?
    no

    Quote Originally Posted by kiwimediapro View Post
    WOuld it work with just the months asthe years are dynamic?
    only if you extracted the distinct years dynamically


    how would you like to proceed?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2011
    Posts
    13

    Unhappy I have this but....

    The error is:
    Table 'crm.crmsales' doesn't exist


    Code:
    CREATE TEMPORARY TABLE Months ( monthname VARCHAR(50) NOT NULL) ENGINE=MEMORY;
    
    INSERT INTO Months (monthname) VALUES ('January');
    INSERT INTO Months (monthname) VALUES ('February');
    INSERT INTO Months (monthname) VALUES ('March');
    INSERT INTO Months (monthname) VALUES ('April');
    INSERT INTO Months (monthname) VALUES ('May');
    INSERT INTO Months (monthname) VALUES ('June');
    INSERT INTO Months (monthname) VALUES ('July');
    INSERT INTO Months (monthname) VALUES ('August');
    INSERT INTO Months (monthname) VALUES ('September');
    INSERT INTO Months (monthname) VALUES ('October');
    INSERT INTO Months (monthname) VALUES ('November');
    INSERT INTO Months (monthname) VALUES ('December');
    
    
    
    SELECT m.monthname, 
    			COALESCE(s.salessubtotal, 0)
    FROM months m
    LEFT OUTER JOIN
    (
    SELECT SUM(t1.totalprice) as totalprice,
    SUM(t1.sales) as salessubtotal, t1.`year` as `year`,
    t1.mnth as `month`
    from 
    (SELECT
    Sum(crmsales.totalprice) AS totalprice,
    MONTHNAME(crmsales.date) AS mnth,
    YEAR(crmsales.date) AS `year`,
    Count(crmsales.totalprice) AS sales,
    MONTH(crmsales.date) AS `ordering`
    FROM `crmsales`
    WHERE `date` BETWEEN '2009-01-01' AND '2010-05-28'
    GROUP BY crmsales.date
    ) as t1
    GROUP BY t1.`year`,t1.mnth
    ORDER BY t1.`year`, t1.ordering) s 
    on m.monthname = s.`month`

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by kiwimediapro View Post
    The error is:
    Table 'crm.crmsales' doesn't exist
    i don't see anywhere in your query where you mention crm.crmsales

    are you sure that's the error message?

    are you sure that's the query you ran?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    May 2011
    Posts
    13

    Reply

    That is what it spits out.... :-)

    The database is 'crm' the table is 'crmsales'

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, apparently you don't gots a database or table with that name
    Last edited by r937; 06-09-11 at 20:18.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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