Mysql - SUM Column - Subquery returns more than 1 row

Question

i want count the hours of our members, where they was helping us. The table seems like this:

    CREATE TABLE `stunden` (
  `std_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `einsatz_id` int(11) NOT NULL,
  `stunden` decimal(5,1) NOT NULL DEFAULT '0.0',
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`std_id`)
) ENGINE=InnoDB;

INSERT INTO stunden VALUES
(1,1,2,1.0,'2018-01-19 00:36:15'),
(2,2,2,1.0,'2018-01-19 00:36:15'),
(3,4,2,1.0,'2018-01-19 00:36:15'),
(4,1,1,2.0,'2018-01-19 00:36:37'),
(5,5,1,2.0,'2018-01-19 00:36:37'),
(6,1,2,2.5,'2018-01-20 12:00:36');

 mysql> select * from stunden;
+--------+---------+------------+---------+---------------------+
| std_id | user_id | einsatz_id | stunden | last_update         |
+--------+---------+------------+---------+---------------------+
|      1 |       1 |          2 |     1.0 | 2018-01-19 00:36:15 |
|      2 |       2 |          2 |     1.0 | 2018-01-19 00:36:15 |
|      3 |       4 |          2 |     1.0 | 2018-01-19 00:36:15 |
|      4 |       1 |          1 |     2.0 | 2018-01-19 00:36:37 |
|      5 |       5 |          1 |     2.0 | 2018-01-19 00:36:37 |
|      6 |       1 |          2 |     2.5 | 2018-01-20 12:00:36 |
+--------+---------+------------+---------+---------------------+
6 rows in set (0,00 sec)

You can see that member with user_id = 1, was available two times at einsatz_id=2 and one times at einsatz_id = 1. What i need is the sum of stunden (stunden=hours) per einsatz_id and per user_id. I have tried with the following query.

SELECT 
(SELECT SUM(s.stunden) AS Zeit FROM stunden s GROUP BY s.user_id, s.einsatz_id) AS STUNDEN,
    m.nachname,
    m.vorname,
    a.einsatz
FROM
    stunden s,
    mitglieder m,
    arbeitseinsatz a
WHERE
    s.user_id = m.id
        AND s.einsatz_id = a.einsatz_id
GROUP BY s.user_id, s.einsatz_id
ORDER BY m.nachname , m.vorname ASC

It results in the following error message:

"ERROR 1242 (21000): Subquery returns more than 1 row".

Any help would be appreciated.

If ONLY_FULL_GROUP_BY is disabled, a MySQL extension to the standard SQL use of GROUP BY permits the select list, HAVING condition, or ORDER BY list to refer to nonaggregated columns even if the columns are not functionally dependent on GROUP BY columns. This causes MySQL to accept the preceding query. In this case, the server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate, which is probably not what you want. https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html — P.Salmon, Jan 20 '18 at 13:51
https://stackoverflow.com/questions/28171474/solution-to-subquery-returns-more-than-1-row-error — Jakob, Jan 20 '18 at 13:54
It would help if you translated the query to english, nachname = last name, vorname = first name , einsatz = commitment?, mitglieder = members?, arbeitseinsatz = work effort? — P.Salmon, Jan 20 '18 at 13:57

score 0 · Answer 1 · answered Jan 20 '18 at 14:00

If this isn't what you're after, please amend your question as described above...

SELECT user_id, einsatz_id, SUM(stunden) total FROM stunden GROUP BY user_id, einsatz_id;
+---------+------------+-------+
| user_id | einsatz_id | total |
+---------+------------+-------+
|       1 |          1 |   2.0 |
|       1 |          2 |   3.5 |
|       2 |          2 |   1.0 |
|       4 |          2 |   1.0 |
|       5 |          1 |   2.0 |
+---------+------------+-------+

score 0 · Answer 2 · answered Jan 20 '18 at 14:05

I see what you're trying to do, I don't see the error yet. But one thing I can say is that you should use JOIN. When you do :

FROM
stunden s,
mitglieder m,
arbeitseinsatz a
WHERE
s.user_id = m.id

It's realy ressources consuming because it is doing a scalar product.

SELECT whatever
FROM utilisateur
(LEFT/RIGHT) JOIN arbeitseinsatz ON stunden.einsatz_id = arbeitseinsatz.einsatz_id

Or something like that, replace (left or right) by what you wanna use, there is a lot of documentation on the subject.

The difference may not be visible on small databases but on larger projects it should improve the perfs quite a lot. And if you use more tables it will make you code cleaner and scalable.

I know that's not the answer you expected but hope you learned something.

score 0 · Answer 3 · answered Jan 21 '18 at 13:59

I found a solution for that, i had to disable ONLY_FULL_GROUP_BY in the following way, i have added two lines in /etc/mysql/my.cnf.

[mysqld]
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

I have changed the query to "JOIN", only to consume less resources (thanks to platypus's post).

SELECT 
    SUM(s.stunden), m.nachname, m.vorname, a.einsatz
FROM
    stunden s
        JOIN
    mitglieder m ON s.user_id = m.id
        JOIN
    arbeitseinsatz a ON s.einsatz_id = a.einsatz_id
GROUP BY a.einsatz_id , s.user_id
ORDER BY m.nachname , m.vorname , a.einsatz DESC;

Mysql - SUM Column - Subquery returns more than 1 row

3 Answers3