Question 283460 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to group courses by their discipline ID?

I’ve just started to learn MySQL and I’m stuck at a seemingly simple query. I’ve got 5 tables for a course provider db. 2 of them being Courses and Disciplines. Courses table has got discipline ID but Discipline table doesn’t have Course IDs.

Wha I"m trying to achieve is the total cost of courses by discipline. There are 5 courses and 3 disciplines. All I’m wanting is the sum of courses grouped by distinct discipline IDs.

I’ve joined the two tables but can’t seem to do any aggregates on it successfully. What am I doing wrong?

SELECT c.Course_Name, c.Price, d.Discipline_Name, c.Course_ID, d.Discipline_ID
FROM Courses c
JOIN Discipline d
ON c.Discipline_ID = d.Discipline_ID
GROUP BY c.Course_ID

When I group by Discipline_ID, it gives me an error saying Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘course_provider.c.Course_Name’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Tags:

3

Answers


  1. 0

    You need to understand what aggregation is. It is the process of grouping raw records into refined groups. A field of a raw record is no longer an attribute of the group.

    For example, if you are part of the students who are taking a course, then while your name is an attribute of yours, but it’s not an attribute of the course students. So, if you select an aggregation from the course students, then your name is not a valid aggregation.

    A valid aggregate field is a field unique to the group (but strongly preferred to be part of the group by clause, because of more restrictive settings) or an aggregation. So, while your age is not a proper field of an aggregated group of students taking a course, the average age of students is logically sound as a field of the group.

    Your error in the query is that you are trying to select a raw field from an aggregated group and it is invalid.

    In our case, Discipline – Course is a one-to-many relationship, that is, there could be many courses of a discipline, but a course belongs to a single discipline. This is why you need to group by the discipline’s ID.

    Improved query:

    SELECT GROUP_CONCAT(c.Course_Name) AS Course_Name, SUM(c.Price) AS Price, d.Discipline_Name, GROUP_CONCAT(c.Course_ID) AS Course_ID, d.Discipline_ID
FROM Courses c
JOIN Discipline d
ON c.Discipline_ID = d.Discipline_ID
GROUP BY d.Discipline_ID, d.Discipline_Name

    But I would not select the name of the course even via group_concat unless it’s needed, because on the big picture of discipline total prices we already know what discipline the courses of each group belongs and the course’s name seems to be uninteresting, especially the course’s id. Nevertheless, I aggregated it for you so you see how can we make an aggregation for a field as well as to preserve the spirit of your query. But you will need to reevaluate it yourself and determine what’s actually needed and what not.

    Login or Signup to reply.
  2. 0

    An error of this kind usually arises because you have used a column in your SQL statement that is referenced in the SELECT section but is not present in the GROUP BY section, and this is not compatible with the only_full_group_by rules.

    To resolve this issue, you need to either add the columns used in the SELECT section to the GROUP BY section or use aggregate functions such as SUM() for non-grouped values.

    In the case of your specific question, you can use the SUM() function to calculate the total cost of courses based on each discipline. Below is a sample code with explanations:

    SELECT d.Discipline_ID, d.Discipline_Name, SUM(c.Price) AS Total_Cost
FROM Courses c
JOIN Discipline d ON c.Discipline_ID = d.Discipline_ID
GROUP BY d.Discipline_ID, d.Discipline_Name;

    In this code, we use SUM(c.Price) to calculate the total cost of courses based on each discipline, and we use GROUP BY based on Discipline_ID and Discipline_Name to group the results. This allows you to observe the total cost of courses for each discipline.

    Login or Signup to reply.
  3. 0

    depending on what you want to archive.

    when you want to know the count of courses per discipline, or the price of the courses.
    when you do not need the name of the discipline, you do not even need to join the discipline table.

    when grouping with a group by, you can only specify aggregate functions in the sql, next to the columns you use in the group by clause.

    e.g. this would go:

    SELECT c.Discipline_ID, count(distinct(c.Course_ID))
FROM Courses c
GROUP BY c.Course_ID

    e.g. if you want the price sum per discipline id

    SELECT c.Discipline_ID, sum(c.Price)
FROM Courses c
GROUP BY c.Course_ID
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search