GROUP BY column present in only one table in case of JOIN (using two tables)

Question

This gives an error:

SELECT 
    customerName, customerNumber, SUM(amount)
FROM
    customers
        LEFT JOIN
    payments USING (customerNumber)
GROUP BY customers.customerName
ORDER BY customerName;

BUT 'GROUP BY customers.customerNumber' gives the correct result.

NOTE: 'customerNumber' is present in both the tables but 'customerName' is not.

Columns of table 'customers':

customerNumber int(11) **PK** 
customerName varchar(50) 
contactLastName varchar(50) 
contactFirstName varchar(50)

Columns of table 'payments':

customerNumber int(11) **PK** 
checkNumber varchar(50) **PK** 
paymentDate date 
amount decimal(10,2)

Is this a constraint that we can GROUP BY column which is present in both the tables in case of JOIN?

What were you expecting to get in the output? In short, you might do something like SELECT...customerNumner, IFNULL(customerName, 'UNKNOWN').... then at least you would have a valuie for the customerName field. — Matt Runion, Aug 05 '18 at 13:22
I am expecting: customerName, customerNumber, SUM(amount) and using LEFT JOIN on 'customers' , so records in customerName cant be NULL. — A K, Aug 05 '18 at 13:27

score 0 · Answer 1 · answered Aug 05 '18 at 13:30

0

your question

is this a constraint that we can GROUP BY column which is present in both the tables in case of JOIN? Answer: No there is no such rule

i think below will work for you, the rule of aggregate function is the number of columns you project is must also be in group by

SELECT 
        c.customerName, c.customerNumber, SUM(amount)
    FROM
        customers c
            LEFT JOIN
        payments p on c.customerNumber=p.customerNumber
    GROUP BY c.customerName,c.customerNumber
    ORDER BY customerName;

answered Aug 05 '18 at 13:30

Zaynul Abadin Tuhin

31,407
5
33
63

I already said that as you select two columns in select statement but used one column in group by clause thats the reason – Zaynul Abadin Tuhin Aug 05 '18 at 13:40
@AK you have learn some tutorial of sql grouping then you will get more – Zaynul Abadin Tuhin Aug 05 '18 at 13:42
@AK you can search in google by typing aggregate function a lot of site will return where you know details about that – Zaynul Abadin Tuhin Aug 05 '18 at 13:43
Ah @AK you SUM itself is aggregate function bro – Zaynul Abadin Tuhin Aug 05 '18 at 13:46
@AK here sum is aggregate function and for this you used group by clause and others 2 columns are the projection column – Zaynul Abadin Tuhin Aug 05 '18 at 13:48
@AK yeah you are right i'm bit weak in English but i tried to clear you the facts of aggregate function – Zaynul Abadin Tuhin Aug 05 '18 at 13:52
@AK for weakness in english i going to share a link where you learn a bit more https://stackoverflow.com/questions/12843303/sql-vs-mysql-rules-about-aggregate-operations-and-group-by – Zaynul Abadin Tuhin Aug 05 '18 at 14:01
https://stackoverflow.com/questions/12843303/sql-vs-mysql-rules-about-aggregate-operations-and-group-by – Zaynul Abadin Tuhin Aug 05 '18 at 14:01

score 0 · Accepted Answer · answered Aug 05 '18 at 15:27

There is no such constraint. I would (in general) recommend including all unaggregated columns in the GROUP BY clause:

GROUP BY customerName, customerNumber

(You should use aliases, but that is not relevant to this question.)

SQL, in general, requires this. With one exception. If one (or more) columns are unique or a primary key, then you can do:

SELECT c.*                 -- any or all the columns
. . .
GROUP BY c.customerNumber  -- unique or primary key

That explains why this formulation works. However, customerName is neither unique nor a primary key, so that is an error.

Note: Older versions of MySQL did not impose this constraint, so you could put anything in the GROUP BY and SELECT clauses for an aggregation query.

score -1 · Answer 3 · answered Aug 05 '18 at 13:23

-1

No, you can use Group By on any column of any table in Join. There is no requirement that the column must present in both tables.

What is the error that you get with GROUP BY customers.customerName?

answered Aug 05 '18 at 13:23

Pol_pm

65
6

** THIS IS THE ERROR :** Error Code: 1055. Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'classicmodels.customers.customerNumber' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by – A K Aug 05 '18 at 13:29
Try GROUP BY c.customerName, c.customerNumber – Pol_pm Aug 05 '18 at 13:41
Also consider changing sql_mode. Read here: https://rejahrehim.com/blog/mysql/osx/fix/2016/12/15/MySQL-Error-GROUP-BY-incompatible-with-sql_mode.html – Pol_pm Aug 05 '18 at 13:44

GROUP BY column present in only one table in case of JOIN (using two tables)

3 Answers3