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

    Exclamation Unanswered: SQL genius required! A different missing months issue between two years

    Hello all,

    I have tried various queries to many to list here but currently have this as the start point:

    Code:
    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,
    						FROM `crm_sales`
    						WHERE `date` BETWEEN '2009-08-01' AND '2010-04-08'
    						GROUP BY crm_sales.date
    Firstly NOTE it runs between 2 years. I get this:
    TOTALPRICE MONTH YEAR SALES
    11248.54 August 2009 48
    3989.14 August 2009 20
    4701.97 August 2009 25
    4940.00 August 2009 24
    2208.00 August 2009 10
    8090.53 August 2009 39
    3532.54 August 2009 17
    4193.81 August 2009 20
    4072.98 August 2009 20
    4658.39 August 2009 24
    13256.39 August 2009 64
    4568.32 August 2009 23
    4806.71 August 2009 23
    6049.95 August 2009 30
    4037.00 August 2009 19
    8255.88 August 2009 39
    3420.26 August 2009 19
    4770.00 August 2009 24
    4798.26 August 2009 23
    2976.74 August 2009 15
    210.00 August 2009 1
    12440.91 August 2009 61
    4200.00 September 2009 20
    4000.00 September 2009 20
    6031.92 September 2009 30
    4541.00 September 2009 23
    6450.00 September 2009 30
    6497.00 September 2009 31
    4063.00 September 2009 22
    3840.00 September 2009 19
    3265.00 September 2009 16
    8651.00 September 2009 42
    5040.00 September 2009 24
    5650.55 September 2009 26
    5148.00 September 2009 24
    4465.00 September 2009 21
    5921.00 September 2009 28
    5239.00 September 2009 25
    6962.00 September 2009 34
    6561.00 September 2009 32
    5670.00 September 2009 28
    9565.00 September 2009 46
    3205.00 September 2009 15
    6617.00 September 2009 32
    7074.00 October 2009 33
    3521.00 October 2009 19
    12372.00 October 2009 62
    6025.97 October 2009 27
    6744.00 October 2009 31
    6009.32 October 2009 30
    4320.00 October 2009 22
    9891.00 October 2009 50
    5178.30 October 2009 27
    7390.98 October 2009 35
    4520.00 October 2009 22
    4163.22 October 2009 22
    7267.16 October 2009 35
    5210.61 October 2009 27
    5923.72 October 2009 29
    4267.56 October 2009 19
    4630.00 October 2009 23
    12820.32 October 2009 62
    5298.24 October 2009 26
    5060.00 October 2009 26
    6949.00 October 2009 33
    8952.23 November 2009 42
    4644.70 November 2009 23
    3289.00 November 2009 17
    4706.29 November 2009 23
    1434.99 November 2009 7
    7347.80 November 2009 36
    3270.00 November 2009 17
    3717.95 November 2009 17
    4113.00 November 2009 20
    2875.77 November 2009 14
    7285.15 November 2009 35
    4893.00 November 2009 23
    6479.43 November 2009 31
    3463.36 November 2009 17
    5949.45 November 2009 29
    8079.16 November 2009 39
    5627.33 November 2009 28
    3588.18 November 2009 17
    4420.00 November 2009 22
    3401.00 November 2009 16
    10277.26 November 2009 51
    5171.00 December 2009 25
    2983.00 December 2009 16
    2980.23 December 2009 17
    5459.54 December 2009 26
    7914.96 December 2009 38
    5440.50 December 2009 28
    4139.29 December 2009 20
    4200.00 December 2009 20
    3959.31 December 2009 18
    6788.26 December 2009 32
    4506.51 December 2009 23
    6200.00 December 2009 30
    4706.00 December 2009 24
    3054.12 December 2009 17
    10928.04 December 2009 52
    6683.16 December 2009 31
    4903.89 December 2009 23
    2420.00 December 2009 12
    6620.00 December 2009 32
    5139.00 December 2009 25
    2520.00 December 2009 12
    6220.00 January 2010 32
    10699.07 January 2010 57
    5900.00 January 2010 30
    5493.16 January 2010 26
    4743.98 January 2010 22
    10140.22 January 2010 50
    4826.45 January 2010 24
    5628.02 January 2010 27
    3717.03 January 2010 17
    6229.24 January 2010 31
    9024.16 January 2010 44
    6829.00 January 2010 34
    4995.85 January 2010 24
    5501.28 January 2010 26
    4320.78 January 2010 21
    7981.03 January 2010 38
    5929.62 January 2010 30
    6025.64 January 2010 29
    4473.70 January 2010 23
    5173.37 January 2010 24
    16836.90 February 2010 82
    4626.70 February 2010 22
    4591.04 February 2010 22
    3912.27 February 2010 18
    12642.76 February 2010 58
    5605.51 February 2010 29
    6336.37 February 2010 32
    4838.82 February 2010 25
    4218.46 February 2010 22
    9566.09 February 2010 49
    5359.00 February 2010 26
    4217.90 February 2010 22
    5371.23 February 2010 28
    4620.00 February 2010 22
    9634.88 February 2010 47
    5410.31 February 2010 27
    5312.55 February 2010 25
    6184.82 February 2010 28
    7467.11 February 2010 35
    12713.32 March 2010 63
    4771.00 March 2010 23
    3799.68 March 2010 20
    6100.63 March 2010 29
    5664.83 March 2010 28
    9287.40 March 2010 45
    5709.90 March 2010 27
    6300.00 March 2010 30
    4526.26 March 2010 23
    5491.54 March 2010 26
    7812.89 March 2010 37
    6336.75 March 2010 32
    5805.78 March 2010 25
    6379.38 March 2010 30
    4533.04 March 2010 21
    14003.88 March 2010 66
    5702.65 March 2010 27
    4373.87 March 2010 22
    5333.38 March 2010 27
    4730.00 March 2010 22
    11504.55 March 2010 59
    6125.00 March 2010 29
    4356.59 March 2010 21
    3866.23 April 2010 19
    149.00 April 2010 1
    10460.37 April 2010 54
    6237.52 April 2010 31
    2580.10 April 2010 13

    Note that
    for 2009 figures I am missing months January - July
    for 2010 figures I am missing months May - December

    I know that that means there is no data and I know how to fill in the missing databy using a Month table in a left outer join.

    What i dont know i how to do this for each year....

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by kiwimediapro View Post
    I know how to fill in the missing databy using a Month table in a left outer join.
    could you show this query please

    then i'll show you how to modify it so that it works for the years you want
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2011
    Posts
    13

    Code as requested

    As you requested:

    Code:
    DROP TABLE IF EXISTS datatbl;
    CREATE TEMPORARY TABLE IF NOT EXISTS datatbl
    						SELECT SUM(t1.totalprice) as totalprice,
    						SUM(t1.sales) as salessubtotals, 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-08-01' AND '2010-04-08'
    						GROUP BY crm_sales.date
    						) as t1
    						GROUP BY t1.`year`,t1.mnth
    						ORDER BY  t1.ordering;
    
    
    						SELECT m.monthname as `month` 
    									,COALESCE(s.salessubtotals, 0) sales
    									,COALESCE (s.`year`,2009) `year`
    									,COALESCE(s.totalprice, 0) totalprice
    									,CASE m.monthname
    										WHEN 'January' then '1'
    										WHEN 'February' then '2'
    										WHEN 'March' then '3'
    										WHEN 'April' then '4'
    										WHEN 'May' then '5'
    										WHEN 'June' then '6'
    										WHEN 'July' then '7'
    										WHEN 'August' then '8'
    										WHEN 'September' then '9'
    										WHEN 'October' then '10'
    										WHEN 'November' then '11'
    										WHEN 'December' then '12'
    									END AS ordering
    						FROM months m
    						LEFT OUTER JOIN datatbl s
    						on m.monthname = s.`month`
    						GROUP BY m.monthname
    						ORDER BY ABS(ordering) ASC;

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this looks like it'll be pretty straightforward to fix for you

    two questions first, though

    1. why are you using a temporary table?

    2. doesn't the month table have a month number column? could you do a SHOW CREATE TABLE for it please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2011
    Posts
    13

    Here are those answers

    Here is the show create table for the months:
    Code:
    CREATE TABLE `months` (
      `monthname` varchar(50) NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by kiwimediapro View Post
    Here is the show create table for the months:
    could you change it please...
    Code:
    DROP TABLE months;
    
    CREATE TABLE months
    ( m SMALLINT NOT NULL PRIMARY KEY 
    , monthname VARCHAR(9) NOT NULL
    );
    INSERT INTO months (monthname,m) VALUES 
     ( 'January'   , 1 )
    ,( 'February'  , 2 )
    ,( 'March'     , 3 )
    ,( 'April'     , 4 )
    ,( 'May'       , 5 )
    ,( 'June'      , 6 )
    ,( 'July'      , 7 )
    ,( 'August'    , 8 )
    ,( 'September' , 9 )
    ,( 'October'   , 10 )
    ,( 'November'  , 11 )
    ,( 'December'  , 12 )
    ;
    the important part here is not only does the m column save you from that horrible CASE expression you had, but it also indexes the table

    also, why the temporary table? you don't need it for anything else, do you?

    the reason i ask is because you can do everything in one query, and creating a table, even a temporary one, has overhead
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    May 2011
    Posts
    13

    Reply

    Well as you can see I am a noob with SQL and could not find a way to do it all in one query so created the temp table.... :-(

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by kiwimediapro View Post
    ... could not find a way to do it all in one query so created the temp table.... :-(
    not a problem, i was just axin'
    Code:
    SELECT years.y AS year
         , months.monthname AS month 
         , COALESCE(s.sales,0) AS sales
         , COALESCE(s.totalprice,0) AS totalprice
      FROM ( SELECT 2009 AS y
             UNION ALL
             SELECT 2010
             UNION ALL
             SELECT 2011 ) AS years
    CROSS
      JOIN months 
    LEFT OUTER 
      JOIN ( SELECT YEAR(crm_sales.date) AS y
                  , MONTH(crm_sales.date) AS m
                  , SUM(crm_sales.totalprice) AS totalprice
                  , COUNT(crm_sales.totalprice) AS sales
               FROM crm_sales
              WHERE crm_sales.date BETWEEN '2009-08-01' 
                                       AND '2010-04-08'
             GROUP 
                 BY y
                  , m ) AS s
        ON s.y = years.y
       AND s.m = months.m
    ORDER 
        BY years.y
         , months.m
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    May 2011
    Posts
    13

    Your a bloody genius

    WOw so simple.

    Thank you very much!

    Only thing is i may be wrong as usual it seems but this seems to fix the years does it not?

    SELECT 2009 AS y
    UNION ALL
    SELECT 2010
    UNION ALL
    SELECT 2011

    SO from PHP drop downs i could have dates BETWEEN '2002-08-01'
    AND '2010-04-08'

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yeah, that was just one way to approach the generation of the year numbers

    replace that with this --
    Code:
    FROM ( SELECT DISTINCT YEAR(crm_sales.date) AS y
               FROM crm_sales
              WHERE crm_sales.date BETWEEN '2002-08-01' 
                                       AND '2010-04-08' ) AS years
    you could actually tweak it further to eliminate the months not in your date range (2002 january through july, 2010 from may to december) but i wouldn't bother
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    May 2011
    Posts
    13

    Cool!

    THank you so much.

    I shall endeavor to study this query so as to learn more.

    Appreciate it.

    IF you had a paypal account i would send you a beer.

    :-)

Tags for this Thread

Posting Permissions

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