1

I have a table where one of the columns may have duplicate entries, and I want to condense each set of duplicates into a single row, with an aggregate function applied to the other columns. I cannot think of a sensible explanation of this, so here is an example data table and result table

+------------------------+--------------------+
| column_with_duplicates | column_to_aggregate|
+------------------------+--------------------+
| 1                      | 1                  |
| 1                      | 2                  |
| 2                      | 3                  |
| 3                      | 4                  |
| 3                      | 5                  |
| 3                      | 6                  |
+---------------------------------------------+
output

+------------------------+--------------------+
| column_with_duplicates | column_to_aggregate|
+------------------------+--------------------+
| 1                      | 1.5                |
| 2                      | 2                  |
| 3                      | 5                  |
+---------------------------------------------+

All of the rows where column_with_duplicates is 1 had column_to_aggregate averaged together into a result row. The same happened for all values of column_with_duplicates

Since I had so much difficulty describing this, I couldn't effectively research the issue.

1 Answer 1

3

It seems like your query needs to be something like:

SELECT 
  column_with_duplicates
  , AVG(column_to_aggregate) AS column_to_aggregate -- AVG does the math, AS gives the name
FROM <table name> 
GROUP BY 
  column_with_duplicates;

More information on creating aliases (the AS keyword).

More information on GROUP BY

More information on the AVG function with a handy example that would have served you well.


Since you had a question about the GROUP BY clause in the comments below...

As I understand it MySQL has some quirks with it's GROUP BY rules. Those quirks not withstanding, I think of a simple GROUP BY clause, like the one in the query above, as sorting the result set into buckets. So in our query above there would be one bucket for each unique value in the column_with_duplicates column. So each value in the column_to_aggregate would get dropped into the proper bucket. Once all the rows/values are sorted into their buckets then an average is taken of all the values in each bucket individually.

Of course this is just a conceptual model, but it should give you a basic understanding of the role of a GROUP BY clause.

6
  • That worked, but I'm not sure why avg didn't try to average the whole column. Does GROUP BY change the behavior of aggregate functions? Commented Apr 6, 2016 at 2:15
  • 1
    @klikkkolee What do you mean by "whole column?" If you want an average of all values in the table irrespective of the values in column_with_duplicates then you would drop that column from your SELECT and GROUP BY clauses.
    – Erik
    Commented Apr 6, 2016 at 2:17
  • The examples of aggregate functions I found apparently mislead me. The impression they gave me was that aggregate functions took all rows and returned a single number. Commented Apr 6, 2016 at 2:27
  • 1
    @klikkkolee I just updated my answer with a bucket analogy for group by. Let me know if this helps clear up your confusion. The main take away is that GROUP BY collates the data and gives you one row per grouping. Since we are grouping by the column_with_duplicates all the duplicates are compressed into one row and the column_to_aggregate is aggregated via the AVG function.
    – Erik
    Commented Apr 6, 2016 at 2:31
  • 1
    @klikkkolee well I can complicate the hell out of it if you'd prefer ;) in all seriousness I'm glad I helped and that the problem is easier than you feared
    – Erik
    Commented Apr 6, 2016 at 2:55

Your Answer

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

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