The GROUPing pitfall - Revision history

Jwoody: /* The short version */

2020-09-21T15:55:27Z

The short version

← Older revision		Revision as of 10:55, 21 September 2020
Line 6:		Line 6:

	== The short version ==		== The short version ==
	If your query joins on more than one table with a one-to-many relationship with the row you're grouping by, you CANNOT use any [~~http~~://dev.mysql.com/doc/refman/5.0/en/~~group-by~~-functions.html aggregate functions] in your query.		If your query joins on more than one table with a one-to-many relationship with the row you're grouping by, you CANNOT use any [https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html aggregate functions] in your query.

	If you do, then any time that more than one of those JOINs matches more than one row, all of your aggregate functions will break.		If you do, then any time that more than one of those JOINs matches more than one row, all of your aggregate functions will break.

Duff: /* The short version */ lol spaces

2012-06-30T22:41:51Z

The short version: lol spaces

← Older revision		Revision as of 17:41, 30 June 2012
Line 6:		Line 6:

	== The short version ==		== The short version ==
	If your query joins on more than one table with a one-to-many relationship with the row you're grouping by, you ~~CAN NOT~~ use any [http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html aggregate functions] in your query.		If your query joins on more than one table with a one-to-many relationship with the row you're grouping by, you CANNOT use any [http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html aggregate functions] in your query.

	If you do, then any time that more than one of those JOINs matches more than one row, all of your aggregate functions will break.		If you do, then any time that more than one of those JOINs matches more than one row, all of your aggregate functions will break.

Duff: /* Protip: write that query correctly */ LOL WORDING

2010-02-07T05:18:22Z

Protip: write that query correctly: LOL WORDING

← Older revision		Revision as of 00:18, 7 February 2010
Line 301:		Line 301:
	How do you make sure that your aggregate functions return the data you want? Well, until MySQL starts reading minds (7.0?), you make sure that '''only one of the data sources in your query has a one-to-many relationship with your result.'''		How do you make sure that your aggregate functions return the data you want? Well, until MySQL starts reading minds (7.0?), you make sure that '''only one of the data sources in your query has a one-to-many relationship with your result.'''

	Like most of life's difficulties, this was made easier in MySQL 4.1!		Like most of life's difficulties, this was made easier with the release of MySQL 4.1!

	Want to join some phat data into your query, but don't want to risk multiplying your data set by all the matching rows in another table? Join a subquery!		Want to join some phat data into your query, but don't want to risk multiplying your data set by all the matching rows in another table? Join a subquery!

Duff: /* A completely valid query */ Added link to Crystal Reports

2010-02-07T05:15:54Z

A completely valid query: Added link to Crystal Reports

← Older revision		Revision as of 00:15, 7 February 2010
Line 127:		Line 127:
	\|}		\|}

	Boom, bitches. Fire up Crystal and turn that sucker into a report, amirite?		Boom, bitches. Fire up [[Crystal Reports\|Crystal]] and turn that sucker into a report, amirite?

	=== BUT WAIT! ===		=== BUT WAIT! ===

Duff: /* WRONG! */ Added a second table to try to clarify how the JOIN was working.

2010-01-22T20:34:44Z

WRONG!: Added a second table to try to clarify how the JOIN was working.

← Older revision		Revision as of 15:34, 22 January 2010
Line 220:		Line 220:
	\|+ Query results		\|+ Query results
	! monkey_name		! monkey_name
	! ~~poop_size~~		! monkey_id
	! poop_id		! poop_id
	! meal_id		! meal_id
	\|-		\|-
	\| style="color:blue" \| Bimbo		\| style="color:blue" \| Bimbo
	\| style="color:~~red~~" \| ~~4.3~~		\| style="color:blue" \| 2
	\| style="color:red" \| 1		\| style="color:red" \| 1
	\| style="color:green" \| 1		\| style="color:green" \| 1
	\|-		\|-
	\| style="color:blue" \| Bimbo		\| style="color:blue" \| Bimbo
	\| style="color:~~red~~" \| 2.6		\| style="color:blue" \| 2
	\| style="color:red" \| 4		\| style="color:red" \| 4
	\| style="color:green" \| 1		\| style="color:green" \| 1
	\|-		\|-
	\| style="color:blue" \| The Hooker		\| style="color:blue" \| The Hooker
	\| style="color:~~red~~" \| 3.8		\| style="color:blue" \| 3
	\| style="color:red" \| 2		\| style="color:red" \| 2
	\| style="color:green" \| 2		\| style="color:green" \| 2
			\|-
			\| style="color:blue" \| The Hooker
			\| style="color:blue" \| 3
			\| style="color:red" \| 2
			\| style="color:green" \| 4
	\|-		\|-
	\| style="color:blue" \| Bobo		\| style="color:blue" \| Bobo
	\| style="color:~~red~~" \| ~~4.0~~		\| style="color:blue" \| 1
	\| style="color:red" \| 3		\| style="color:red" \| 3
	\| style="color:green" \| 3		\| style="color:green" \| 3
			\|}

			Or, to try to show how the JOINs work a bit more clearly:
			{\| border=1
			\|+ Query results
			! monkey_name
			! monkey_id
			! poop_id
			! meal_id
			\|-
			\| style="color:blue" rowspan="2" \| Bimbo
			\| style="color:blue" rowspan="2" \| 2
			\| style="color:red" \| 1
			\| style="color:green" \| 1
			\|-
			\| style="color:red" \| 4
			\| style="color:green" \| 1
			\|-
			\| style="color:blue" rowspan="2" \| The Hooker
			\| style="color:blue" rowspan="2" \| 3
			\| style="color:red" rowspan="2" \| 2
			\| style="color:green" \| 2
	\|-		\|-
	~~\| style="color:blue" \| The Hooker~~
	~~\| style="color:red" \| 3.8~~
	~~\| style="color:red" \| 2~~
	\| style="color:green" \| 4		\| style="color:green" \| 4
			\|-
			\| style="color:blue" \| Bobo
			\| style="color:blue" \| 1
			\| style="color:red" \| 3
			\| style="color:green" \| 3
	\|}		\|}

Duff: Colorized the query result cells.

2010-01-22T20:08:45Z

Colorized the query result cells.

Show changes

Duff: /* A completely valid query */ Missing an AS! Poor form!

2010-01-22T18:31:53Z

A completely valid query: Missing an AS! Poor form!

← Older revision		Revision as of 13:31, 22 January 2010
Line 97:		Line 97:

	<source lang="mysql">		<source lang="mysql">
	SELECT monkey.name AS monkey_name, AVG(poop.size) AS average_poop_size, SUM(poop.size) total_poop,		SELECT monkey.name AS monkey_name, AVG(poop.size) AS average_poop_size, SUM(poop.size) AS total_poop,
	COUNT(DISTINCT poop.id) AS number_of_poops		COUNT(DISTINCT poop.id) AS number_of_poops
	FROM monkey		FROM monkey

Duff: /* Duh! */ Phrased things a bit better, mmhmm

2010-01-22T18:13:05Z

Duh!: Phrased things a bit better, mmhmm

← Older revision		Revision as of 13:13, 22 January 2010
Line 255:		Line 255:
	By drawing on your basic understanding of how JOINs work, you should quickly see what causes this. Every row that is related to a monkey id is going to be multiplied by the number of matching rows from tables are joined to that monkey id.		By drawing on your basic understanding of how JOINs work, you should quickly see what causes this. Every row that is related to a monkey id is going to be multiplied by the number of matching rows from tables are joined to that monkey id.

	Of course, all those multiplied rows disappear from sight as soon as you GROUP BY some column - but the first aggregate function ~~exposes~~ the dirty truth: you didn't put any thought into how these tables related!		Of course, all those multiplied rows disappear from sight as soon as you GROUP BY some column - but the first aggregate function you use will expose the dirty truth: you didn't put any thought into how these tables related!

	== How to NOT make the mistake ==		== How to NOT make the mistake ==

Duff: /* Protip: write that query correctly */ Wrapped queries to fit on smaller screens

2010-01-22T17:39:15Z

Protip: write that query correctly: Wrapped queries to fit on smaller screens

← Older revision		Revision as of 12:39, 22 January 2010
Line 290:		Line 290:
	JOIN		JOIN
	(		(
	SELECT meal.monkeyid, AVG(meal.weight) AS average_meal_weight, SUM(meal.weight) AS total_meal_weight, COUNT(*) AS number_of_meals		SELECT meal.monkeyid, AVG(meal.weight) AS average_meal_weight, SUM(meal.weight) AS total_meal_weight,
			COUNT(*) AS number_of_meals
	FROM meal		FROM meal
	GROUP BY meal.monkeyid		GROUP BY meal.monkeyid
Line 336:		Line 337:

	<source lang="MySQL">		<source lang="MySQL">
	SELECT monkey.name AS monkey_name, AVG(poop.size) AS average_poop_size, SUM(poop.size) AS total_poop, COUNT(poop.id) AS poops,		SELECT monkey.name AS monkey_name, AVG(poop.size) AS average_poop_size, SUM(poop.size) AS total_poop,
	meal_grouped.average_meal_weight, meal_grouped.total_meal_weight, meal_grouped.number_of_meals		COUNT(poop.id) AS poops, meal_grouped.average_meal_weight, meal_grouped.total_meal_weight,
			meal_grouped.number_of_meals
	FROM monkey		FROM monkey
	JOIN poop ON poop.monkeyid = monkey.id		JOIN poop ON poop.monkeyid = monkey.id
	JOIN		JOIN
	(		(
	SELECT meal.monkeyid, AVG(meal.weight) AS average_meal_weight, SUM(meal.weight) AS total_meal_weight, COUNT(*) AS number_of_meals		SELECT meal.monkeyid, AVG(meal.weight) AS average_meal_weight, SUM(meal.weight) AS total_meal_weight,
			COUNT(*) AS number_of_meals
	FROM meal		FROM meal
	GROUP BY meal.monkeyid		GROUP BY meal.monkeyid

Duff: Version 1.0, whee! Now people won't write bad queries any more!

2010-01-22T17:28:16Z

Version 1.0, whee! Now people won't write bad queries any more!

@@ Line 10: / Line 10: @@
 If you do, then any time that more than one of those JOINs matches more than one row, all of your aggregate functions will break.
-== Wait, why? ==
+== The long version ==
-You know about [http://dev.mysql.com/doc/refman/5.0/en/join.html JOINs], right?  Let's say you have two tables:
+You know how JOINs work, right?  Let's say you have two tables:
 {|- border=1
@@ Line 165: / Line 165: @@
 |}
-Dude, the eating habits of these monkeys is totally the sort of thing you would want to use in your report on pooping frequency.  Let's join that shit into your query!
+Dude, the eating habits of these monkeys are totally the sort of thing you would want to use in your report on pooping frequency.  Let's join that shit into your query!
 <source lang="mysql">
@@ Line 332: / Line 332: @@
 That's right - every data set (the original table, as well as those subqueries) has a 1-to-1 relationship with our final result set.  Because of this, we don't even need a GROUP BY in the main query!

← Older revision		Revision as of 15:34, 22 January 2010
Line 220:		Line 220:
	\|+ Query results		\|+ Query results
	! monkey_name		! monkey_name
	! ~~poop_size~~		! monkey_id
	! poop_id		! poop_id
	! meal_id		! meal_id
	\|-		\|-
	\| style="color:blue" \| Bimbo		\| style="color:blue" \| Bimbo
	\| style="color:~~red~~" \| ~~4.3~~		\| style="color:blue" \| 2
	\| style="color:red" \| 1		\| style="color:red" \| 1
	\| style="color:green" \| 1		\| style="color:green" \| 1
	\|-		\|-
	\| style="color:blue" \| Bimbo		\| style="color:blue" \| Bimbo
	\| style="color:~~red~~" \| 2.6		\| style="color:blue" \| 2
	\| style="color:red" \| 4		\| style="color:red" \| 4
	\| style="color:green" \| 1		\| style="color:green" \| 1
	\|-		\|-
	\| style="color:blue" \| The Hooker		\| style="color:blue" \| The Hooker
	\| style="color:~~red~~" \| 3.8		\| style="color:blue" \| 3
	\| style="color:red" \| 2		\| style="color:red" \| 2
	\| style="color:green" \| 2		\| style="color:green" \| 2
			\|-
			\| style="color:blue" \| The Hooker
			\| style="color:blue" \| 3
			\| style="color:red" \| 2
			\| style="color:green" \| 4
	\|-		\|-
	\| style="color:blue" \| Bobo		\| style="color:blue" \| Bobo
	\| style="color:~~red~~" \| ~~4.0~~		\| style="color:blue" \| 1
	\| style="color:red" \| 3		\| style="color:red" \| 3
	\| style="color:green" \| 3		\| style="color:green" \| 3
			\|}

			Or, to try to show how the JOINs work a bit more clearly:
			{\| border=1
			\|+ Query results
			! monkey_name
			! monkey_id
			! poop_id
			! meal_id
			\|-
			\| style="color:blue" rowspan="2" \| Bimbo
			\| style="color:blue" rowspan="2" \| 2
			\| style="color:red" \| 1
			\| style="color:green" \| 1
			\|-
			\| style="color:red" \| 4
			\| style="color:green" \| 1
			\|-
			\| style="color:blue" rowspan="2" \| The Hooker
			\| style="color:blue" rowspan="2" \| 3
			\| style="color:red" rowspan="2" \| 2
			\| style="color:green" \| 2
	\|-		\|-
	~~\| style="color:blue" \| The Hooker~~
	~~\| style="color:red" \| 3.8~~
	~~\| style="color:red" \| 2~~
	\| style="color:green" \| 4		\| style="color:green" \| 4
			\|-
			\| style="color:blue" \| Bobo
			\| style="color:blue" \| 1
			\| style="color:red" \| 3
			\| style="color:green" \| 3
	\|}		\|}