[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
        );

---

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: :confused:

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');

---

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 :mad:

if you can make that change, you will find that many of your sql problems simply go away

Quote:

---

Originally Posted by r937

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 |
+-----+----------+----+

---

the solution. :D

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

---

there is no need to TRIM a datetime value -- that doesn't even make sense

Quote:

---

Originally Posted by r937

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

---