SitePoint Sponsor

User Tag List

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

    [MySQL 5.0.45-community-nt-log] Sort the “rollup” in group by

    Hello guys!

    I found that the "with rollup" option used with group by is very useful. But it does not behave with "order by" clause.
    Is there any way to order by my way?
    Code:
    SELECT
    	COALESCE (name, 'GRAND TOTAL') AS name,
    	tot
    FROM
    	(
    		SELECT
    			name,
    			COUNT(*) AS tot
    		FROM
    			tbl_p
    
    		)
    		GROUP BY
    			name WITH ROLLUP
    	) Q
    ORDER BY
    	CASE
    WHEN name = 'GRAND TOTAL' THEN
    	1
    ELSE
    	0
    END;
    Output:
    Code:
    +------------------------------+--------+
    | name                         | tot    |
    +------------------------------+--------+
    | SEAN DUBOIS                  |      3 |
    | SEAN MAXWELL                 |      2 |
    | GRAND TOTAL                  |     39 |
    | DILLINGER BOY                |     12 |
    | DILLINGER GIRL               |     22 |
    +------------------------------+--------+
    I need this output:
    Code:
    +------------------------------+--------+
    | name                         | tot    |
    +------------------------------+--------+
    | GRAND TOTAL                  |     39 |
    | DILLINGER BOY                |     12 |
    | DILLINGER GIRL               |     22 |
    | SEAN DUBOIS                  |      3 |
    | SEAN MAXWELL                 |      2 |
    +------------------------------+--------+
    Could you please help?
    Thanks you very much for your help.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    ORDER 
        BY CASE WHEN name = 'GRAND TOTAL' 
                THEN 0
                ELSE 1 END
         , name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

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

    But:
    Code:
    SELECT
    	COALESCE (name, 'GRAND TOTAL') AS name,
    	tot
    FROM
    	(
    		SELECT
    			name,
    			COUNT(*) AS tot
    		FROM
    			tbl_p
    
    		)
    		GROUP BY
    			name WITH ROLLUP
    	) Q
    ORDER 
    BY CASE WHEN name = 'GRAND TOTAL' 
    THEN 0
    ELSE 1 END, name;
    Output:
    Code:
    +------------------------------+--------+
    | name                         | tot    |
    +------------------------------+--------+
    | SEAN DUBOIS                  |      3 |
    | SEAN MAXWELL                 |      2 |
    | GRAND TOTAL                  |     39 |
    | DILLINGER BOY                |     12 |
    | DILLINGER GIRL               |     22 |
    +------------------------------+--------+
    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,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    use
    Code:
    COALESCE(name,'GRAND TOTAL') AS print_name
    and then use print_name in the ORDER BY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

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

    But:
    Code:
    SELECT
    	COALESCE (name, 'GRAND TOTAL') AS print_name,
    	tot
    FROM
    	(
    		SELECT
    			name,
    			COUNT(*) AS tot
    		FROM
    			tbl_p
    
    		)
    		GROUP BY
    			name WITH ROLLUP
    	) Q
    ORDER 
    BY CASE WHEN name = 'GRAND TOTAL' 
    THEN 0
    ELSE 1 END, print_name;
    Output:
    Code:
    +------------------------------+--------+
    | print_name                   | tot    |
    +------------------------------+--------+
    | SEAN DUBOIS                  |      3 |
    | SEAN MAXWELL                 |      2 |
    | GRAND TOTAL                  |     39 |
    | DILLINGER BOY                |     12 |
    | DILLINGER GIRL               |     22 |
    +------------------------------+--------+
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you must also use print_name inside the CASE expression
    rudy.ca | @rudydotca
    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)
    Quote Originally Posted by r937 View Post
    you must also use print_name inside the CASE expression
    Thanks a lot!
    Code:
    mysql> SELECT
    	COALESCE (`name`, 'GRAND TOTAL') AS `print_name`,
    	tot
    FROM
    	(
    		SELECT
    			`name`,
    			COUNT(*) AS tot
    		FROM
    			tbl_p
    		GROUP BY
    			`name` WITH ROLLUP
    	) Q
    ORDER BY
    	CASE
    WHEN `print_name` = 'GRAND TOTAL' THEN
    	0
    ELSE
    	1
    END,
     `print_name`;
     
    +----------------+-----+
    | print_name     | tot |
    +----------------+-----+
    | GRAND TOTAL    |  39 |
    | DILLINGER BOY  |  12 |
    | DILLINGER GIRL |  22 |
    | SEAN DUBOIS    |   3 |
    | SEAN MAXWELL   |   2 |
    +----------------+-----+
    5 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
  •