2

say I have a table with names:

ID Name
1 Carl
2 Mark

then I have another table where names.id is used

id namesid amount
1 1 100
2 1 50

My query is

SELECT names.id, names.name, IFNULL(sum(amount),'0,00') as sum_amount 
FROM names LEFT OUTER JOIN other_table ON names.id=other_table.namesid 
GROUP BY names.id 
WITH ROLLUP

but this query is returning no data for name.id=2. The expected result considering how I get that LEFT OUTER JOIN works is the following:

ID Name sum_amount
1 Carl 150
2 Mark 0,00

Why is it not working as expected? It should work also if there are no data in other_table ending with:

ID Name sum_amount
1 Carl 0,00
2 Mark 0,00
0

1 Answer 1

2

Your GROUP BY is incomplete and you do not need WITH ROLLUP

SELECT names.id, names.name, IFNULL(sum(amount),'0.00') as sum_amount 
FROM names LEFT OUTER JOIN other_table ON names.id=other_table.namesid 
GROUP BY names.id,names.name;

Here is a sample to prove it

mysql> DROP DATABASE IF EXISTS faieta;
Query OK, 1 row affected (0.05 sec)

mysql> CREATE DATABASE faieta;
Query OK, 1 row affected (0.00 sec)

mysql> USE faieta
Database changed
mysql> CREATE TABLE names (
    ->    id INT NOT NULL AUTO_INCREMENT,
    ->    name VARCHAR(20),
    ->    PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO names (name) VALUES ('Carl');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO names (name) VALUES ('Mark');
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE other_table (
    ->    id INT NOT NULL AUTO_INCREMENT,
    ->    namesid INT NOT NULL,
    ->    amount DOUBLE,
    ->    PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO other_table (namesid,amount) VALUES (1,100);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO other_table (namesid,amount) VALUES (1,50);
Query OK, 1 row affected (0.01 sec)

mysql>

Here is the query output

mysql> SELECT names.id, names.name, IFNULL(sum(amount),'0.00') as sum_amount
    -> FROM names LEFT OUTER JOIN other_table ON names.id=other_table.namesid
    -> GROUP BY names.id,names.name;
+----+------+------------+
| id | name | sum_amount |
+----+------+------------+
|  1 | Carl | 150        |
|  2 | Mark | 0.00       |
+----+------+------------+
2 rows in set (0.00 sec)

mysql>
3
  • Why do you need , names.name? That should be dependant on names.id.
    – Rick James
    Jan 5 at 20:11
  • @RickJames you're right it is dependent.. The question posed it as part of the expected output. That column is actually not needed for the GROUP BY. The GROUP BY can be nestled into a subquery and joined back for that column value. Jan 5 at 21:36
  • 1
    I'm trying to say that "only-full-group-by" should not kick in; that is GROUP BY names.id should suffice without "joining back".
    – Rick James
    Jan 5 at 22:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.