SitePoint Sponsor

User Tag List

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

    ORDER BY clause in CASE WHEN

    Hi, I can't order by this query, why?
    I need order for rdt value UOT first value, MAL second value, PMC three value and RAS four value.
    Thanks in advance
    Code:
    mysql> SELECT
    	IFNULL(`RDT`, 'tot') AS RDT,
    	IFNULL(`type`, 'tot') AS `type`,
    	SUM(`number`) AS `number`,
    	`total`,
    	SUM(`perc`) AS `perc`,
    	`thedate`
    FROM
    	(
    		SELECT
    			IFNULL(`RDT`, 'tot') AS RDT,
    			`type`,
    			`NUMBER`,
    			`total`,
    			`perc`,
    			`THEDATE`
    		FROM
    			`dotable`
    		GROUP BY
    			`RDT`,
    			`type`
    		ORDER BY
    			CASE
    		WHEN RDT LIKE 'UOT' THEN
    			1
    		WHEN RDT LIKE 'MAL' THEN
    			2
    		WHEN RDT LIKE 'PMC' THEN
    			3
    		ELSE
    			4
    		END, RDT
    	) AS X
    GROUP BY
    	x.RDT,
    	x.type WITH ROLLUP;
    +-----+---------------+--------+-------+--------+------------+
    | RDT | type          | number | total | perc   | thedate    |
    +-----+---------------+--------+-------+--------+------------+
    | MAL | checking      | 112    |  3249 | 3.40   | 2014-03-27 |
    | MAL | control       | 33     |  3249 | 1.00   | 2014-03-27 |
    | MAL | reconstructed | 5      |  3249 | 0.20   | 2014-03-27 |
    | MAL | regular       | 960    |  3249 | 29.50  | 2014-03-27 |
    | MAL | study         | 10     |  3249 | 0.30   | 2014-03-27 |
    | MAL | tot           | 1120   |  3249 | 34.40  | 2014-03-27 |
    | PMC | checking      | 107    |  3153 | 3.40   | 2014-03-27 |
    | PMC | control       | 167    |  3153 | 5.30   | 2014-03-27 |
    | PMC | reconstructed | 8      |  3153 | 0.30   | 2014-03-27 |
    | PMC | regular       | 833    |  3153 | 26.40  | 2014-03-27 |
    | PMC | study         | 72     |  3153 | 2.30   | 2014-03-27 |
    | PMC | tot           | 1187   |  3153 | 37.70  | 2014-03-27 |
    | RAS | checking      | 1      |   970 | 0.10   | 2014-03-27 |
    | RAS | control       | 42     |   970 | 4.30   | 2014-03-27 |
    | RAS | reconstructed | 1      |   970 | 0.10   | 2014-03-27 |
    | RAS | regular       | 318    |   970 | 32.80  | 2014-03-27 |
    | RAS | study         | 3      |   970 | 0.30   | 2014-03-27 |
    | RAS | tot           | 365    |   970 | 37.60  | 2014-03-27 |
    | UOT | checking      | 11     |  3527 | 0.30   | 2014-03-27 |
    | UOT | control       | 283    |  3527 | 8.00   | 2014-03-27 |
    | UOT | regular       | 235    |  3527 | 6.70   | 2014-03-27 |
    | UOT | study         | 8      |  3527 | 0.20   | 2014-03-27 |
    | UOT | tot           | 537    |  3527 | 15.20  | 2014-03-27 |
    | tot | tot           | 3209   |  3527 | 124.90 | 2014-03-27 |
    +-----+---------------+--------+-------+--------+------------+
    24 rows in set
    
    mysql>

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Code:
    ORDER BY FIND_IN_SET(rdt,'UOT,MAL,PMC,RAS')
    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
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    I'm sorry but new order by clause not work:
    Code:
    mysql> SELECT
    	IFNULL(`RDT`, 'tot') AS RDT,
    	IFNULL(`type`, 'tot') AS `type`,
    	SUM(`number`) AS `number`,
    	`total`,
    	SUM(`perc`) AS `perc`,
    	`thedate`
    FROM
    	(
    		SELECT
    			IFNULL(`RDT`, 'tot') AS RDT,
    			`type`,
    			`NUMBER`,
    			`total`,
    			`perc`,
    			`THEDATE`
    		FROM
    			`dotable`
    		GROUP BY
    			`RDT`,
    			`type`
    		ORDER BY
    			FIND_IN_SET(rdt, 'UOT,MAL,PMC,RAS')
    	) AS X
    GROUP BY
    	x.RDT,
    	x.type WITH ROLLUP;
    +-----+---------------+--------+-------+--------+------------+
    | RDT | type          | number | total | perc   | thedate    |
    +-----+---------------+--------+-------+--------+------------+
    | MAL | checking      | 112    |  3249 | 3.40   | 2014-03-27 |
    | MAL | control       | 33     |  3249 | 1.00   | 2014-03-27 |
    | MAL | reconstructed | 5      |  3249 | 0.20   | 2014-03-27 |
    | MAL | regular       | 960    |  3249 | 29.50  | 2014-03-27 |
    | MAL | study         | 10     |  3249 | 0.30   | 2014-03-27 |
    | MAL | tot           | 1120   |  3249 | 34.40  | 2014-03-27 |
    | PMC | checking      | 107    |  3153 | 3.40   | 2014-03-27 |
    | PMC | control       | 167    |  3153 | 5.30   | 2014-03-27 |
    | PMC | reconstructed | 8      |  3153 | 0.30   | 2014-03-27 |
    | PMC | regular       | 833    |  3153 | 26.40  | 2014-03-27 |
    | PMC | study         | 72     |  3153 | 2.30   | 2014-03-27 |
    | PMC | tot           | 1187   |  3153 | 37.70  | 2014-03-27 |
    | RAS | checking      | 1      |   970 | 0.10   | 2014-03-27 |
    | RAS | control       | 42     |   970 | 4.30   | 2014-03-27 |
    | RAS | reconstructed | 1      |   970 | 0.10   | 2014-03-27 |
    | RAS | regular       | 318    |   970 | 32.80  | 2014-03-27 |
    | RAS | study         | 3      |   970 | 0.30   | 2014-03-27 |
    | RAS | tot           | 365    |   970 | 37.60  | 2014-03-27 |
    | UOT | checking      | 11     |  3527 | 0.30   | 2014-03-27 |
    | UOT | control       | 283    |  3527 | 8.00   | 2014-03-27 |
    | UOT | regular       | 235    |  3527 | 6.70   | 2014-03-27 |
    | UOT | study         | 8      |  3527 | 0.20   | 2014-03-27 |
    | UOT | tot           | 537    |  3527 | 15.20  | 2014-03-27 |
    | tot | tot           | 3209   |  3527 | 124.90 | 2014-03-27 |
    +-----+---------------+--------+-------+--------+------------+
    24 rows in set
    
    mysql>

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    ORDER BY in a subquery makes no logical sense whatsoever

    ORDER BY should be in the outer query only
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    ORDER BY in a subquery makes no logical sense whatsoever

    ORDER BY should be in the outer query only
    Okey Sir, but if ORDER BY on the outer query only I've error:
    Code:
    mysql> SELECT
    	IFNULL(`RDT`, 'tot') AS RDT,
    	IFNULL(`type`, 'tot') AS `type`,
    	SUM(`number`) AS `number`,
    	`total`,
    	SUM(`perc`) AS `perc`,
    	`thedate`
    FROM
    	(
    		SELECT
    			IFNULL(`RDT`, 'tot') AS RDT,
    			`type`,
    			`NUMBER`,
    			`total`,
    			`perc`,
    			`THEDATE`
    		FROM
    			`dotable`
    		GROUP BY
    			`RDT`,
    			`type`
    	) AS X
    GROUP BY
    	x.RDT,
    	x.type WITH ROLLUP
    ORDER BY
    	FIND_IN_SET(RDT, 'UOT,MAL,PMC,RAS');
    1221 - Incorrect usage of CUBE/ROLLUP and ORDER BY
    mysql>

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    gee, i wonder if there's anything you could do about that, like putting the function into the SELECT clause...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    gee, i wonder if there's anything you could do about that, like putting the function into the SELECT clause...
    I don't understand this ...

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Miguel61 View Post
    I don't understand this ...
    this thread started out by you asking for how to order your values

    i gave you the FIND_IN_SET function

    you put it into the ORDER BY clause of a subquery

    i said ORDER BY makes sense only in the outer query

    you discovered that you cannot have an ORDER BY clause along with ROLLUP

    i suggested you put the function into the SELECT clause

    think it over some more, please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    this thread started out by you asking for how to order your values

    i gave you the FIND_IN_SET function

    you put it into the ORDER BY clause of a subquery

    i said ORDER BY makes sense only in the outer query

    you discovered that you cannot have an ORDER BY clause along with ROLLUP

    i suggested you put the function into the SELECT clause

    think it over some more, please
    Okay, I'm trying this without success .....
    Code:
    mysql> SELECT
    	*
    FROM
    	(
    		SELECT
    			FIND_IN_SET(RDT, 'UOT,MAL,PMC,RAS,tot') AS myOrder,
    			IFNULL(`RDT`, 'tot') AS RDT,
    			IFNULL(`type`, 'rdt tot') AS `type`,
    			SUM(`number`) AS `number`,
    			`total`,
    			SUM(`perc`) AS `perc`,
    			`thedate`
    		FROM
    			(
    				SELECT
    					IFNULL(`RDT`, 'tot') AS RDT,
    					`type`,
    					`NUMBER`,
    					`total`,
    					`perc`,
    					`THEDATE`
    				FROM
    					`dotable`
    				GROUP BY
    					`RDT`,
    					`type`
    			) AS X
    		GROUP BY
    			x.RDT,
    			x.type WITH ROLLUP
    	) y
    ORDER BY
    	myOrder;
    +---------+-----+---------------+--------+-------+--------+------------+
    | myOrder | RDT | type          | number | total | perc   | thedate    |
    +---------+-----+---------------+--------+-------+--------+------------+
    | NULL    | tot | rdt tot       | 3209   |  3527 | 124.90 | 2014-03-27 |
    |       1 | UOT | study         | 8      |  3527 | 0.20   | 2014-03-27 |
    |       1 | UOT | control       | 283    |  3527 | 8.00   | 2014-03-27 |
    |       1 | UOT | rdt tot       | 537    |  3527 | 15.20  | 2014-03-27 |
    |       1 | UOT | regular       | 235    |  3527 | 6.70   | 2014-03-27 |
    |       1 | UOT | checking      | 11     |  3527 | 0.30   | 2014-03-27 |
    |       2 | MAL | study         | 10     |  3249 | 0.30   | 2014-03-27 |
    |       2 | MAL | reconstructed | 5      |  3249 | 0.20   | 2014-03-27 |
    |       2 | MAL | checking      | 112    |  3249 | 3.40   | 2014-03-27 |
    |       2 | MAL | rdt tot       | 1120   |  3249 | 34.40  | 2014-03-27 |
    |       2 | MAL | regular       | 960    |  3249 | 29.50  | 2014-03-27 |
    |       2 | MAL | control       | 33     |  3249 | 1.00   | 2014-03-27 |
    |       3 | PMC | regular       | 833    |  3153 | 26.40  | 2014-03-27 |
    |       3 | PMC | control       | 167    |  3153 | 5.30   | 2014-03-27 |
    |       3 | PMC | study         | 72     |  3153 | 2.30   | 2014-03-27 |
    |       3 | PMC | reconstructed | 8      |  3153 | 0.30   | 2014-03-27 |
    |       3 | PMC | checking      | 107    |  3153 | 3.40   | 2014-03-27 |
    |       3 | PMC | rdt tot       | 1187   |  3153 | 37.70  | 2014-03-27 |
    |       4 | RAS | study         | 3      |   970 | 0.30   | 2014-03-27 |
    |       4 | RAS | reconstructed | 1      |   970 | 0.10   | 2014-03-27 |
    |       4 | RAS | checking      | 1      |   970 | 0.10   | 2014-03-27 |
    |       4 | RAS | rdt tot       | 365    |   970 | 37.60  | 2014-03-27 |
    |       4 | RAS | regular       | 318    |   970 | 32.80  | 2014-03-27 |
    |       4 | RAS | control       | 42     |   970 | 4.30   | 2014-03-27 |
    +---------+-----+---------------+--------+-------+--------+------------+
    24 rows in set
    
    mysql>

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    you realize that nulls always sort first, right?

    do you understand where the null is coming from?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you realize that nulls always sort first, right?

    do you understand where the null is coming from?
    I'm trying this with success but I dont no if is your suggestion ...:
    Code:
    mysql> SELECT
    	*
    FROM
    	(
    		SELECT
    			IFNULL(`RDT`, 'tot') AS RDT,
    			IFNULL(`type`, 'rdt tot') AS `type`,
    			SUM(`number`) AS `number`,
    			`total`,
    			SUM(`perc`) AS `perc`,
    			`thedate`
    		FROM
    			(
    				SELECT
    					IFNULL(`RDT`, 'tot') AS RDT,
    					`type`,
    					`NUMBER`,
    					`total`,
    					`perc`,
    					`THEDATE`
    				FROM
    					`dotable`
    				GROUP BY
    					`RDT`,
    					`type`
    			) AS X
    		GROUP BY
    			x.RDT,
    			x.type WITH ROLLUP
    	) y
    ORDER BY
    	FIND_IN_SET(RDT, 'UOT,MAL,PMC,RAS,tot,tot_rdt');
    +-----+---------------+--------+-------+--------+------------+
    | RDT | type          | number | total | perc   | thedate    |
    +-----+---------------+--------+-------+--------+------------+
    | UOT | regular       | 235    |  3527 | 6.70   | 2014-03-27 |
    | UOT | checking      | 11     |  3527 | 0.30   | 2014-03-27 |
    | UOT | study         | 8      |  3527 | 0.20   | 2014-03-27 |
    | UOT | control       | 283    |  3527 | 8.00   | 2014-03-27 |
    | UOT | rdt tot       | 537    |  3527 | 15.20  | 2014-03-27 |
    | MAL | checking      | 112    |  3249 | 3.40   | 2014-03-27 |
    | MAL | rdt tot       | 1120   |  3249 | 34.40  | 2014-03-27 |
    | MAL | regular       | 960    |  3249 | 29.50  | 2014-03-27 |
    | MAL | control       | 33     |  3249 | 1.00   | 2014-03-27 |
    | MAL | study         | 10     |  3249 | 0.30   | 2014-03-27 |
    | MAL | reconstructed | 5      |  3249 | 0.20   | 2014-03-27 |
    | PMC | study         | 72     |  3153 | 2.30   | 2014-03-27 |
    | PMC | reconstructed | 8      |  3153 | 0.30   | 2014-03-27 |
    | PMC | checking      | 107    |  3153 | 3.40   | 2014-03-27 |
    | PMC | rdt tot       | 1187   |  3153 | 37.70  | 2014-03-27 |
    | PMC | regular       | 833    |  3153 | 26.40  | 2014-03-27 |
    | PMC | control       | 167    |  3153 | 5.30   | 2014-03-27 |
    | RAS | regular       | 318    |   970 | 32.80  | 2014-03-27 |
    | RAS | control       | 42     |   970 | 4.30   | 2014-03-27 |
    | RAS | study         | 3      |   970 | 0.30   | 2014-03-27 |
    | RAS | reconstructed | 1      |   970 | 0.10   | 2014-03-27 |
    | RAS | checking      | 1      |   970 | 0.10   | 2014-03-27 |
    | RAS | rdt tot       | 365    |   970 | 37.60  | 2014-03-27 |
    | tot | rdt tot       | 3209   |  3527 | 124.90 | 2014-03-27 |
    +-----+---------------+--------+-------+--------+------------+
    24 rows in set
    
    mysql>

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    are you happy with it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    not completely because:
    1. I would like your fundamental opinion;
    2. The last row of output is wrong in colum `perc` because show 124.90 value and not 90.98 value (numer/total)*100 = (3209/3527)*100

    Code:
    +-----+---------------+--------+-------+--------+------------+
    | RDT | type          | number | total | perc   | thedate    |
    +-----+---------------+--------+-------+--------+------------+
    | tot | rdt tot       | 3209   |  3527 | 124.90 | 2014-03-27 |
    +-----+---------------+--------+-------+--------+------------+

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    1. my opinion is, you'd be better off not using ROLLUP for all the pain and grief it has caused you over the years

    2. no idea, man
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    1. my opinion is, you'd be better off not using ROLLUP for all the pain and grief it has caused you over the years
    it's true the ROLLUP cause me stomach ache ... ... but such alternative ?

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    alternative is to calculate the totals yourself in php (or whatever application language you're using) while printing out the query results
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Okay, many thanks.
    Good luck


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
  •