SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Problem with year in date

    Hello guys.
    I need your help.

    I have in `dotable` MySQL a list of records; unfortunately the records with year of `reg_date` 2000, 2001 and 2002 are incomplete and I don't have chance of a recovery data:
    PHP Code:
    mysqlSELECT
        IFNULL
    (YEAR'Tot') AS YEAR,
        
    k
    FROM
        
    (
            
    SELECT
                YEAR 
    (reg_date) AS YEAR,
                
    COUNT(*) AS k
            FROM
                
    `doTable`
            
    GROUP BY
                YEAR WITH ROLLUP
        
    x;
    +------+------+
    YEAR k    |
    +------+------+
    Tot  2984 |
    2000 |    |
    2001 |    |
    2003 |  364 |
    2004 |  401 |
    2005 |  217 |
    2006 |  135 |
    2007 |  143 |
    2008 |  225 |
    2009 |  261 |
    2010 |  393 |
    2011 |  363 |
    2012 |  262 |
    2013 |   50 |
    Tot  5800 |
    +------+------+
    15 rows in set 
    I need distribute this 2986 rows for three years incomplete, e.g.:
    PHP Code:
    +------+------+
    YEAR k    |
    +------+------+
    2000 2000 |
    2001 |  900 |
    2002 |   86 |
    2003 |  364 |
    2004 |  401 |
    2005 |  217 |
    2006 |  135 |
    2007 |  143 |
    2008 |  225 |
    2009 |  261 |
    2010 |  393 |
    2011 |  363 |
    2012 |  262 |
    2013 |   50 |
    Tot  5800 |
    +------+------+
    15 rows in set 
    Do you have any suggestion for resolve this problem?
    Cheers.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    if you mean "distribute" them to other years?

    do that in php, please

    there's no way that you're going to easily write sql that distributes 1,999 rows to 2000 and 899 rows to 2001
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    if you mean "distribute" them to other years?

    do that in php, please

    there's no way that you're going to easily write sql that distributes 1,999 rows to 2000 and 899 rows to 2001
    I understand, thank you.


Bookmarks

Posting Permissions

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