educative.io

How can I merge John's amount in the first example?

Courses
,
#1

Alias Syntax - Database Design Fundamentals for Software Engineers (educative.io)

I tried to add “GROUP BY C.NAME” in front of the WHERE clause, but it prompted an error.

#2

Hi @lifch !!
To merge John’s amount, you can use the GROUP BY clause to group the results by the customer’s name. However, since you are selecting the customer’s ID, name, age, and order amount from both the CUSTOMERS and ORDERS tables, you need to include all those columns in the GROUP BY clause, not just the customer’s name.

Here’s an updated version of your query that includes the GROUP BY clause correctly:

SELECT C.ID, C.NAME, C.AGE, SUM(O.AMOUNT) AS TOTAL_AMOUNT
FROM CUSTOMERS AS C, ORDERS AS O
WHERE C.ID = O.CUSTOMER_ID
GROUP BY C.ID, C.NAME, C.AGE;

In this query, the SUM(O.AMOUNT) calculates the total amount for each customer. The results will show the customer’s ID, name, age, and the merged (summed) amount for each customer.

Note that when using the GROUP BY clause, you need to specify all the non-aggregated columns (columns that are not part of an aggregate function like SUM, COUNT, etc.) in the SELECT clause in the GROUP BY clause as well.
I hope it helps. Happy Learning :blush:

1 Like
#3

Hi Javeria,

Thank you very much! Yes, it works.

But I am not quite clear why I need to specify all the non-aggregated columns. I tried the query command as below, it works as well.

SELECT C.ID, C.NAME, C.AGE, SUM(O.AMOUNT) AS TOTAL_AMOUNT
FROM CUSTOMERS AS C, ORDERS AS O
WHERE C.ID = O.CUSTOMER_ID
GROUP BY C.ID;