SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

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

    [MySQL 5.5.24-log] Error in output query

    hello everyone, I hope your help.

    I can't understand why different output between these two queries.

    Can you help me?
    Thanks in advance.

    The correct output:
    Code:
    SELECT
    	Q1.RegisterDate,
    	MAX(Q1.warning) warning
    FROM
    	(
    		SELECT
    			RegisterDate,
    
    		IF (
    			lcase(class) LIKE '%iso%',
    			repetitions,
    			0
    		) warning	
    FROM
    	tbl_m1
    WHERE
    	elements <> 'NMT'
    GROUP BY
    	repetitions
    	) Q1
    GROUP BY
    	RegisterDate;
    
    RegisterDate	warning
    2012-07-01	94
    2012-07-02	98
    2012-07-03	8
    2012-07-04	8
    2012-07-05	8
    The wrong output:
    Code:
    CREATE TABLE TestTable (
    	`RegisterDate` VARCHAR (50),
    	`WARNING` INT
    );
    
    INSERT INTO TestTable (
    	`RegisterDate`,
    	`WARNING`
    ) SELECT
    	RegisterDate,
    	warning
    FROM
    	(
    		SELECT
    			RegisterDate,
    
    		IF (
    			(class) LIKE '%iso%',
    			MAX(repetitions),
    			0
    		) warning
    	FROM
    		tbl_m1
    	WHERE
    		1
    	AND elements <> 'NMT'
    	GROUP BY
    		repetitions
    	) AS SubQs
    WHERE
    	1
    GROUP BY
    	RegisterDate;
    
    SELECT
    	RegisterDate,
    	warning
    FROM
    	(
    		SELECT
    			RegisterDate,
    			warning
    		FROM
    			TestTable
    		UNION
    			SELECT
    				RegisterDate,
    				SUM(warning)
    			FROM
    				TestTable
    			GROUP BY
    				(RegisterDate) WITH ROLLUP
    	) q;
    
    DROP TABLE TestTable;
    
    
    RegisterDate	warning
    2012-07-01	0
    2012-07-02	0
    2012-07-03	0
    2012-07-04	0
    2012-07-05	0
    		0
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,509
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    You're missing lcase in the second query.

  3. #3
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    thank you
    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
  •