CODEWITHSUNDEEP
mysqlsqlgroup-byaggregate-functions
Mohammad Naji
Mohammad Naji

Reputation: 5442

WHY don't aggregate functions work, unless using GROUP BY statement?

To calculate the price of invoices (that have *invoice item*s in a separate table and linked to the invoices), I had written this query:

SELECT `i`.`id`, SUM(ii.unit_price * ii.quantity) invoice_price
FROM (`invoice` i)

JOIN `invoiceitem` ii
ON `ii`.`invoice_id` = `i`.`id`

WHERE `i`.`user_id` = '$user_id'

But it only resulted ONE row.

After research, I got that I had to have GROUP BY i.id at the end of the query. With this, the results were as expected.

From my opinion, even without GROUP BY i.id, nothing is lost and it should work well!

Please in some simple sentences tell me...

Why should I always use the additional!!! GROUP BY i.id, What is lost without it, and maybe as the most functioning question, How should I remember that I have lost the additional GROUP BY?!

Upvotes: 1

Views: 343

Answers (2)

Bill Gregg
Bill Gregg

Reputation: 7147

You have to include the group by because there are many IDs that went into the sum. If you don't specify it then MySQL just picks the first one, and sums across the entire result set. GroupBy tells MySQL to sum (or generically aggregate) for each Grouped By Entity.

Upvotes: 4

Jason McCreary
Jason McCreary

Reputation: 73031

Why should I always use GROUP BY?

SUM() and others are Aggregate Functions. Their very nature requires that they be used in combination with GROUP BY.

What is lost without it?

From the documentation:

If you use a group function in a statement containing no GROUP BY clause, it is equivalent to grouping on all rows.

In the end, there is nothing to remember, as these are GROUP BY aggregate functions. You will quickly tell from the result that you have forgotten GROUP BY when the result includes the entire result set (incorrectly), instead of your grouped subsets.

Upvotes: 3

Related Questions