SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Thread: Query COUNT (*)

  1. #1
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Query COUNT (*)

    Hi there, I need your help.

    I tried this query but I need other type of output.
    Can you help me?
    thank you

    Correct output:

    Code:
    +------+--------+
    | year | number |
    +------+--------+
    | 2001 |   2987 |
    | 2003 |    364 |
    | 2004 |    400 |
    | 2005 |    217 |
    | 2006 |    135 |
    | 2007 |    143 |
    | 2008 |    225 |
    | 2009 |    261 |
    | 2010 |    393 |
    | 2011 |    363 |
    | 2012 |    253 |
    | 2013 |      1 |
    | tot_ |   5542 |
    +------+--------+
    My query:
    Code:
    mysql> SELECT
    	CASE
    WHEN YEAR (myDate) IS NULL
    OR YEAR (myDate) = '2001' THEN
    	'2001'
    ELSE
    	YEAR (myDate)
    END AS year,
     COUNT(*) AS number
    FROM
    	tbl_login
    GROUP BY
    	YEAR (myDate);
    +------+--------+
    | year | number |
    +------+--------+
    | 2001 |   2986 |
    | 2001 |      1 |
    | 2003 |    364 |
    | 2004 |    400 |
    | 2005 |    217 |
    | 2006 |    135 |
    | 2007 |    143 |
    | 2008 |    225 |
    | 2009 |    261 |
    | 2010 |    393 |
    | 2011 |    363 |
    | 2012 |    253 |
    | 2013 |      1 |
    +------+--------+
    13 rows in set

  2. #2
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,089
    Mentioned
    54 Post(s)
    Tagged
    0 Thread(s)
    Why not just fix that one date in the table?

    For learning purposes though, your grouping by the actual column instead of the new case statement you made.

    Code:
    group by 
    
    CASE WHEN YEAR (myDate) IS NULL OR YEAR (myDate) = '2001' THEN     '2001'
    and then if you want an actual row to show the total, use a union all to add a new count to the end.

  3. #3
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    thank you very much.

    this is the new output, but I don't have the total ... :
    Code:
    mysql> SELECT
    	CASE
    WHEN YEAR (myDate) IS NULL
    OR YEAR (myDate) = '2001' THEN
    	'2001'
    ELSE
    	YEAR (myDate)
    END AS year,
     COUNT(*) AS number
    FROM
    	tbl_login
    GROUP BY
    	CASE
    WHEN YEAR (myDate) IS NULL
    OR YEAR (myDate) = '2001' THEN
    	'2001'
    ELSE
    	YEAR (myDate)
    END;
    +------+--------+
    | anno | number |
    +------+--------+
    | 2001 |   2987 |
    | 2003 |    364 |
    | 2004 |    400 |
    | 2005 |    217 |
    | 2006 |    135 |
    | 2007 |    143 |
    | 2008 |    225 |
    | 2009 |    261 |
    | 2010 |    393 |
    | 2011 |    363 |
    | 2012 |    253 |
    | 2013 |      1 |
    +------+--------+
    12 rows in set
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by cms9651 View Post
    ...but I don't have the total
    hint: ROLLUP
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    by the way, YEAR() produces an integer, you are making extra work for mysql by comparing it to a string

    also, your CASE can be simplified

    change this --
    Code:
    CASE
    WHEN YEAR (myDate) IS NULL
    OR YEAR (myDate) = '2001' THEN
    	'2001'
    ELSE
    	YEAR (myDate)
    END;
    to this --
    Code:
    COALESCE(YEAR(myDate),2001)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    final thought: now watch what happens when you try to combine ROLLUP with that hack for the null year

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Wow!

    thank you very much!
    Code:
    mysql> SELECT
    	COALESCE (myYear, 'tot') AS `myYear`,
    	number
    FROM
    	(
    		SELECT
    			COALESCE (YEAR(myDate), 2001) AS myYear,
    			COUNT(*) AS number
    		FROM
    			tbl_login
    		GROUP BY
    			COALESCE (YEAR(myDate), 2001) WITH ROLLUP
    	) q;
    
    +------+--------+
    | myYear | number |
    +------+--------+
    | 2001 |   2987 |
    | 2003 |    364 |
    | 2004 |    400 |
    | 2005 |    217 |
    | 2006 |    135 |
    | 2007 |    143 |
    | 2008 |    225 |
    | 2009 |    261 |
    | 2010 |    393 |
    | 2011 |    363 |
    | 2012 |    253 |
    | 2013 |      1 |
    | tot  |   5742 |
    +------+--------+
    13 rows in set
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye


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
  •