MySQL Forums
Forum List  »  German

Re: Subquery returns more than 1 row
Posted by: Thomas Wiedmann
Date: January 11, 2012 05:59AM

Hallo Mark,
stimmt, ein raffiniertes Problem auf Grundlage einer falsche Verwendung von GROUP BY, die MySQL abhängig vom eingestellten SQL_MODE zuläßt und damit "falsche" Ergebnisse erzeugt.

ONLY_FULL_GROUP_BY
Erlaubt keine Abfragen, bei denen die GROUP BY-Klausel auf eine Spalte verweist, die in der Ausgabespaltenliste nicht vorhanden ist.
http://dev.mysql.com/doc/refman/5.1/de/server-sql-mode.html

prüfen und setzen des SQL-Mode in der SESSION

mysql> SELECT @@session.sql_mode;
+--------------------+
| @@session.sql_mode |
+--------------------+
|                    |
+--------------------+
1 row in set (0.00 sec)

mysql> SET @@session.sql_mode=ONLY_FULL_GROUP_BY
    -> ;
Query OK, 0 rows affected (0.03 sec)

mysql>

und siehe da, MySQL meckert nun den SQL an

mysql> SELECT items.brand, (
    ->
    -> SELECT SUM( its.PhysicalStock )
    -> FROM items_stock AS its
    -> WHERE its.itemId = items.itemId
    -> ) AS stockValue
    -> FROM items
    -> WHERE items.id_gender = '1'
    -> GROUP BY items.brand ;
ERROR 1055 (42000): 'rsslounge.items.itemId' isn't in GROUP BY
mysql>


Lösung... ein weiterer SUM() ist notwendig.
-------------------------------------------

SELECT items.brand, SUM(
                        (SELECT SUM( its.PhysicalStock ) 
                           FROM items_stock AS its 
                          WHERE its.itemId = items.itemId 
                        )
                       ) AS stockValue 
 FROM items 
WHERE items.id_gender = '1' 
GROUP BY items.brand ;
+-------+------------+
| brand | stockValue |
+-------+------------+
| Suit  |         50 |
| Test  |         34 |
+-------+------------+
2 rows in set (0.00 sec)


Grüße
Thomas

Options: ReplyQuote


Subject
Views
Written By
Posted
3265
December 15, 2011 08:48AM
1869
December 15, 2011 12:41PM
1702
January 06, 2012 09:51AM
1342
January 11, 2012 03:55AM
Re: Subquery returns more than 1 row
3070
January 11, 2012 05:59AM
1369
January 11, 2012 08:26AM
1579
January 12, 2012 04:52AM
2153
January 12, 2012 06:33AM
1599
January 13, 2012 03:27AM


Sorry, you can't reply to this topic. It has been closed.
This forum is currently read only. You can not log in or make any changes. This is a temporary situation.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.