CODEWITHSUNDEEP
sqlitegroup-bydistinct
yaylitzis
yaylitzis

Reputation: 5554

Different result with DISTINCT and GROUP BY

I execute these 2 queries in my DB. The first one returns 1 record and the second one returns 2241 (as I expected). I ask the same thing but in two different way. In the first, I use DISTINCT and in the second I use "GROUP BY". Aren't these the same?

/*returns 1 record*/
SELECT DISTINCT INVENTORY.location, INVENTORY.label, INVENTORY.version, SUM(INVENTORY.quantity) AS total, LABELS.description, LABELS.customer , LABELS.label 
FROM INVENTORY 
INNER JOIN LABELS 
ON INVENTORY.label = LABELS.label 
AND INVENTORY.version = LABELS.version 
WHERE INVENTORY.location = 1

/*returns 2241  record*/
SELECT Inventory.location, Inventory.label, Inventory.version, SUM(INVENTORY.quantity) AS total, Labels.description, Labels.customer, LABELS.label 
FROM Inventory 
INNER JOIN LABELS 
ON Inventory.label = Labels.label 
AND  Inventory.version =  Labels.version 
WHERE Inventory.location = 1
GROUP BY Inventory.label, Inventory.Version

Upvotes: 0

Views: 88

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

Both your queries are invalid according to ANSI SQL, but that didn't stop Oracle from releasing MySQL, nor did it stop you from writing these queries. Here is a valid version of the two queries you attempted:

SELECT Inventory.location,
       Inventory.label,
       Inventory.version,
       SUM(INVENTORY.quantity) AS total,
       Labels.description,
       Labels.customer,
       LABELS.label 
FROM Inventory 
INNER JOIN LABELS 
    ON Inventory.label   = Labels.label AND
       Inventory.version = Labels.version 
WHERE Inventory.location = 1
GROUP BY Inventory.location,
         Inventory.label,
         Inventory.version,
         Labels.description,
         Labels.customer,
         LABELS.label

Key points here are that every column which appears in the SELECT clause either also appears in the GROUP BY clause or appears inside an aggregate function, such as SUM.

Your queries ran on SQLite because it is compatible with MySQL which is lax in imposing strictness on queries.

Upvotes: 1

Related Questions