Skip to content
Fix Code Error

Using group by on multiple columns

March 13, 2021 by Code Error
Posted By: Anonymous

I understand the point of GROUP BY x.

But how does GROUP BY x, y work, and what does it mean?

Solution

Group By X means put all those with the same value for X in the one group.

Group By X, Y means put all those with the same values for both X and Y in the one group.

To illustrate using an example, let’s say we have the following table, to do with who is attending what subject at a university:

Table: Subject_Selection

+---------+----------+----------+
| Subject | Semester | Attendee |
+---------+----------+----------+
| ITB001  |        1 | John     |
| ITB001  |        1 | Bob      |
| ITB001  |        1 | Mickey   |
| ITB001  |        2 | Jenny    |
| ITB001  |        2 | James    |
| MKB114  |        1 | John     |
| MKB114  |        1 | Erica    |
+---------+----------+----------+

When you use a group by on the subject column only; say:

select Subject, Count(*)
from Subject_Selection
group by Subject

You will get something like:

+---------+-------+
| Subject | Count |
+---------+-------+
| ITB001  |     5 |
| MKB114  |     2 |
+---------+-------+

…because there are 5 entries for ITB001, and 2 for MKB114

If we were to group by two columns:

select Subject, Semester, Count(*)
from Subject_Selection
group by Subject, Semester

we would get this:

+---------+----------+-------+
| Subject | Semester | Count |
+---------+----------+-------+
| ITB001  |        1 |     3 |
| ITB001  |        2 |     2 |
| MKB114  |        1 |     2 |
+---------+----------+-------+

This is because, when we group by two columns, it is saying "Group them so that all of those with the same Subject and Semester are in the same group, and then calculate all the aggregate functions (Count, Sum, Average, etc.) for each of those groups". In this example, this is demonstrated by the fact that, when we count them, there are three people doing ITB001 in semester 1, and two doing it in semester 2. Both of the people doing MKB114 are in semester 1, so there is no row for semester 2 (no data fits into the group "MKB114, Semester 2")

Hopefully that makes sense.

Answered By: Anonymous

Related Articles

  • prevent duplicate (Unique Passport)
  • Rails Active Record find(:all, :order => ) issue
  • How do I include certain conditions in SQL Count
  • Form field border-radius is not working only on the…
  • Maps in Lists in Maps
  • Calculate the mean by group
  • How to calculate Cohen's D across 50 points in R
  • Difference between FetchType LAZY and EAGER in Java…
  • Peak signal detection in realtime timeseries data
  • data.table vs dplyr: can one do something well the…
  • How to edit `cell[i]` in data grid view C# win form…
  • Vue.js/Vuetify: How can I make the v-card-actions reactive?
  • "Large data" workflows using pandas
  • Ukkonen's suffix tree algorithm in plain English
  • Reference — What does this symbol mean in PHP?
  • IMPORTXML shows an error while scraping data from website
  • SQL query return data from multiple tables
  • python 3.2 UnicodeEncodeError: 'charmap' codec can't…
  • Ember hook when transitioning from child route to…
  • BehaviorSubject vs Observable?
  • how to use value of a column as input to a spatial operation
  • How to combine columns in pandas pivot table?
  • Smart way to truncate long strings
  • What are database normal forms and can you give examples?
  • Change column type in pandas
  • How do SO_REUSEADDR and SO_REUSEPORT differ?
  • How do you sign a Certificate Signing Request with…
  • Binding between two arbitrary objects in Ember.js
  • Continuous Integration vs. Continuous Delivery vs.…
  • i am trying to center my an article with header and…
  • What does "Fatal error: Unexpectedly found nil while…
  • Set a 10-second timer for each subject input
  • Including variables after filtering selecting only…
  • C# parse FHIR bundle - read resources
  • How do I keep only the first map and when the game…
  • How can I determine whether a 2D Point is within a Polygon?
  • Plotting curve over several subplots in R
  • Ember link-to handlebars and jQuery DataTables
  • Login button need to be clicked twice in order to login
  • The correct condition to print only those words that…
  • SQL Invalid object name when joining 3 tables
  • How to add geojson points to the map
  • How does PHP 'foreach' actually work?
  • VueJS - trouble understanding .$set and .$add
  • ASP.NET jQuery Ajax Calling Code-Behind Method
  • Pandas column of lists, create a row for each list element
  • Can I construct base class to allocate while…
  • How to find Control in TemplateField of GridView?
  • What is an optional value in Swift?
  • VBA get a list of files in folder and their tags (Keywords)
  • What does this symbol mean in JavaScript?
  • Using Auto Layout in UITableView for dynamic cell…
  • Error: request entity too large
  • Apply multiple functions to multiple groupby columns
  • T-SQL How to create tables dynamically in stored procedures?
  • Using setInterval for creating animation
  • Split cell value that based on a delimiter and…
  • Checkout another branch when there are uncommitted…
  • Generating a drop down list of timezones with PHP
  • HTML table with 100% width, with vertical scroll…
  • How can I fix MySQL error #1064?
  • Why it is hard to use vue-i18n in vue data() (why it…
  • Javascript Axios get with conditional params
  • Vuejs Property or method is not defined on the…
  • How to get list of previous n values of a column…
  • run console one time inside map function using…
  • MySQL "Group By" and "Order By"
  • How to replace the missing values of train and test…
  • SwiftUI Parsing and displaying values form a JSON call
  • Highcharts make positive numbers in ranges of green…
  • Centering in CSS Grid
  • Passing Multiple Parameters to Next.js Custom URL…
  • Naming returned columns in Pandas aggregate function?
  • How to display an unordered list in two columns?
  • Ember.js: Proper way to iterate through object in model?
  • What are the undocumented features and limitations…
  • how to validate both input fields when one updates
  • how to return JSON object if search string matches…
  • How to add multiple columns to pandas dataframe in…
  • I want to align the text in my list element that…
  • Cannot find control with name: formControlName in…
  • is there a way to write a template function for…
  • Use of PUT vs PATCH methods in REST API real life scenarios
  • Sum over previous periods for each period for each…
  • reformat a pandas dataframe
  • Sort table rows In Bootstrap
  • is it possible to assign multiple column/values…
  • Excel VBA - Separate data by pattern by using Split Function
  • How do I expand the output display to see more…
  • Pandas - Reshape a dataframe columns based on…
  • Getting a "TypeError" when trying to validate a form
  • SQL Query for Student mark functionality
  • set single value in object using node js
  • Why isn't my UITableView appearing when I begin to…
  • Usage of __slots__?
  • Finding index of character in Swift String
  • How to create range in Swift?
  • What is the difference between Subject and BehaviorSubject?
  • ComboBox.SelectedItem giving Null value
  • How to compile a table of data in google sheets?

Disclaimer: This content is shared under creative common license cc-by-sa 3.0. It is generated from StackExchange Website Network.

Post navigation

Previous Post:

How do I check for null values in JavaScript?

Next Post:

How do I install and use curl on Windows?

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

.net ajax android angular arrays aurelia backbone.js bash c++ css dataframe ember-data ember.js excel git html ios java javascript jquery json laravel linux list mysql next.js node.js pandas php polymer polymer-1.0 python python-3.x r reactjs regex sql sql-server string svelte typescript vue-component vue.js vuejs2 vuetify.js

  • you shouldn’t need to use z-index
  • No column in target database, but getting “The schema update is terminating because data loss might occur”
  • Angular – expected call-signature: ‘changePassword’ to have a typedeftslint(typedef)
  • trying to implement NativeAdFactory imports deprecated method by default in flutter java project
  • What should I use to get an attribute out of my foreign table in Laravel?
© 2022 Fix Code Error