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)

    [Err] 1111 - Invalid use of group function

    Hello there, hope in your help.

    I tried this query update and I've this error:
    Code:
    [Err] 1111 - Invalid use of group function
    Please tell me how to resolve this problem ... thank you.
    Code:
    [SQL] UPDATE tbl_A A
    JOIN tbl_CC CC ON A.idticket = CC.idticket
    SET A.FieldUpdate = 
    	(
    		TIME_TO_SEC(
    			TIMEDIFF(
    					STR_TO_DATE(
    						MAX(CC.myDate),
    						'%d/%m/%Y %H:%i:%s'
    					),
    					STR_TO_DATE(
    						MIN(CC.myDate),
    						'%d/%m/%Y %H:%i:%s'
    				)
    			)
    		) / 60
    	);
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  2. #2
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I tried this new solution, but I don't understand why the query update all rows, of the field `DAZ`,and not only the row call to JOIN:
    Code:
    mysql> UPDATE tbl_1 t1,
     (
    	SELECT
    		idticket,
    		TIME_TO_SEC(
    			TIMEDIFF(
    				TRIM(
    					STR_TO_DATE(
    						MAX(Date_hour),
    						'%d/%m/%Y %H:%i'
    					)
    				),
    				TRIM(
    					STR_TO_DATE(
    						MIN(Date_hour),
    						'%d/%m/%Y %H:%i'
    					)
    				)
    			)
    		) / 60 AS selected_value
    	FROM
    		tbl_2
    ) AS t2
    JOIN tbl_1 ON tbl_1.idticket = t2.idticket
    SET t1.DAZ = t2.selected_value;
    
    SELECT
    	*
    FROM
    	tbl_1;
    Query OK, 10 rows affected
    Rows matched: 10  Changed: 10  Warnings: 0
    
    +-----+----------+----+
    | DAZ | idticket | ID |
    +-----+----------+----+
    |   4 | 36237886 |  1 |
    |   4 | 36237887 |  2 |
    |   4 | 36237888 |  3 |
    |   4 | 36237889 |  4 |
    |   4 | 36237890 |  5 |
    |   4 | 36237891 |  6 |
    |   4 | 36237892 |  7 |
    |   4 | 36237893 |  8 |
    |   4 | 36237894 |  9 |
    |   4 | 36237895 | 10 |
    +-----+----------+----+
    10 rows in set
    the tables:
    Code:
    SET FOREIGN_KEY_CHECKS=0;
    
    -- ----------------------------
    -- Table structure for `tbl_2`
    -- ----------------------------
    DROP TABLE IF EXISTS `tbl_2`;
    CREATE TABLE `tbl_2` (
      `idticket` varchar(255) DEFAULT NULL,
      `Date_hour` varchar(255) DEFAULT NULL,
      `id` int(10) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
    
    -- ----------------------------
    -- Records of tbl_2
    -- ----------------------------
    INSERT INTO `tbl_2` VALUES ('36237886', '06/02/2013 15:26', '1');
    INSERT INTO `tbl_2` VALUES ('36237886', '06/02/2013 15:30', '2');
    
    
    
    -- ----------------------------
    -- Table structure for `tbl_1`
    -- ----------------------------
    DROP TABLE IF EXISTS `tbl_1`;
    CREATE TABLE `tbl_1` (
      `DAZ` varchar(255) DEFAULT NULL,
      `idticket` varchar(255) DEFAULT NULL,
      `ID` int(10) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`ID`)
    ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
    
    -- ----------------------------
    -- Records of tbl_1
    -- ----------------------------
    INSERT INTO `tbl_1` VALUES ('', '36237886', '1');
    INSERT INTO `tbl_1` VALUES ('', '36237887', '2');
    INSERT INTO `tbl_1` VALUES ('', '36237888', '3');
    INSERT INTO `tbl_1` VALUES ('', '36237889', '4');
    INSERT INTO `tbl_1` VALUES ('', '36237890', '5');
    INSERT INTO `tbl_1` VALUES ('', '36237891', '6');
    INSERT INTO `tbl_1` VALUES ('', '36237892', '7');
    INSERT INTO `tbl_1` VALUES ('', '36237893', '8');
    INSERT INTO `tbl_1` VALUES ('', '36237894', '9');
    INSERT INTO `tbl_1` VALUES ('', '36237895', '10');
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,339
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    once again, it looks like you are making things way too hard on yourself

    to begin with, you should define your datetime columns as DATETIME, not as VARCHAR(255)

    sheesh

    if you can make that change, you will find that many of your sql problems simply go away
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    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
    once again, it looks like you are making things way too hard on yourself
    Not lose your patience with me please

    I've changed my datetime columns as DATETIME.

    I tried this version but the query updates all rows of the field DAZ and not only the row to join...
    Code:
    mysql> UPDATE tbl_1 t1,
     (
    	SELECT
    		idticket,
    		TIME_TO_SEC(
    			TIMEDIFF(
    				TRIM(MAX(Date_hour)),
    				TRIM(MIN(Date_hour))
    			)
    		) / 60 AS selected_value
    	FROM
    		tbl_2
    ) AS t2
    JOIN tbl_1 ON tbl_1.idticket = t2.idticket
    SET t1.DAZ = t2.selected_value; 
    
    
    SELECT
    	*
    FROM
    	tbl_1;
    
    Query OK, 0 rows affected
    Rows matched: 10  Changed: 0  Warnings: 0
    
    +-----+----------+----+
    | DAZ | idticket | ID |
    +-----+----------+----+
    |   4 | 36237886 |  1 |
    |   4 | 36237887 |  2 |
    |   4 | 36237888 |  3 |
    |   4 | 36237889 |  4 |
    |   4 | 36237890 |  5 |
    |   4 | 36237891 |  6 |
    |   4 | 36237892 |  7 |
    |   4 | 36237893 |  8 |
    |   4 | 36237894 |  9 |
    |   4 | 36237895 | 10 |
    +-----+----------+----+
    10 rows in set
    I need this output:
    Code:
    +-----+----------+----+
    | DAZ | idticket | ID |
    +-----+----------+----+
    |   4 | 36237886 |  1 |
    |     | 36237887 |  2 |
    |     | 36237888 |  3 |
    |     | 36237889 |  4 |
    |     | 36237890 |  5 |
    |     | 36237891 |  6 |
    |     | 36237892 |  7 |
    |     | 36237893 |  8 |
    |     | 36237894 |  9 |
    |     | 36237895 | 10 |
    +-----+----------+----+
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  5. #5
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    the solution.
    Code:
    mysql> UPDATE tbl_1
    SET DAZ = NULL;
    
    UPDATE tbl_1 t1
    JOIN (
    	SELECT
    		idticket,
    		TIME_TO_SEC(
    			TIMEDIFF(
    				TRIM(MAX(Date_hour)),
    				TRIM(MIN(Date_hour))
    			)
    		) / 60 AS event_time
    	FROM
    		tbl_2
    	GROUP BY
    		idticket
    ) AS t2 ON t1.idticket = t2.idticket
    SET t1.DAZ = t2.event_time;
    
    SELECT
    	*
    FROM
    	tbl_1;
    Query OK, 1 row affected
    Rows matched: 10  Changed: 1  Warnings: 0
    
    Query OK, 1 row affected
    Rows matched: 1  Changed: 1  Warnings: 0
    
    +------+----------+----+
    | DAZ  | idticket | ID |
    +------+----------+----+
    |    4 | 36237886 |  1 |
    | NULL | 36237887 |  2 |
    | NULL | 36237888 |  3 |
    | NULL | 36237889 |  4 |
    | NULL | 36237890 |  5 |
    | NULL | 36237891 |  6 |
    | NULL | 36237892 |  7 |
    | NULL | 36237893 |  8 |
    | NULL | 36237894 |  9 |
    | NULL | 36237895 | 10 |
    +------+----------+----+
    10 rows in set
    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,339
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    there is no need to TRIM a datetime value -- that doesn't even make sense
    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
    there is no need to TRIM a datetime value -- that doesn't even make sense
    ok, thank you!
    Code:
    mysql> UPDATE tbl_1
    SET DAZ = NULL;
    
    UPDATE tbl_1 t1
    JOIN (
    	SELECT
    		idticket,
    		TIME_TO_SEC(
    			TIMEDIFF(
    				MAX(Date_hour),
    				MIN(Date_hour)
    			)
    		) / 60 AS event_time
    	FROM
    		tbl_2
    	GROUP BY
    		idticket
    ) AS t2 ON t1.idticket = t2.idticket
    SET t1.DAZ = t2.event_time;
    
    SELECT
    	*
    FROM
    	tbl_1;
    Query OK, 1 row affected
    Rows matched: 10  Changed: 1  Warnings: 0
    
    Query OK, 1 row affected
    Rows matched: 1  Changed: 1  Warnings: 0
    
    +------+----------+----+
    | DAZ  | idticket | ID |
    +------+----------+----+
    |    4 | 36237886 |  1 |
    | NULL | 36237887 |  2 |
    | NULL | 36237888 |  3 |
    | NULL | 36237889 |  4 |
    | NULL | 36237890 |  5 |
    | NULL | 36237891 |  6 |
    | NULL | 36237892 |  7 |
    | NULL | 36237893 |  8 |
    | NULL | 36237894 |  9 |
    | NULL | 36237895 | 10 |
    +------+----------+----+
    10 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
  •