MySQL: In a select with aggregate functions, how to join two columns not in aggregate functions?-CodePudding

I'm using Viescas' SQL Queries for Mere Mortals, and its datasets.

If I run his code:

select customers.CustFirstName || " " || customers.CustLastName as "Name", 
customers.CustStreetAddress || "," || customers.CustZipCode || "," || customers.CustState as "Address",
count(engagements.EntertainerID) as "Number of Contracts",
sum(engagements.ContractPrice) as "Total Price",
max(engagements.ContractPrice) as "Max Price"
from customers
inner join engagements
on customers.CustomerID = engagements.CustomerID
group by customers.CustFirstName, customers.custlastname,
customers.CustStreetAddress,customers.CustState,customers.CustZipCode
order by customers.CustFirstName, customers.custlastname;

I get something like the following table:

Name Address Number of Contracts Total Price Max Price

0   1   7   8255.00 2210.00
0   1   11  11800.00    2570.00
0   1   10  12320.00    2450.00
0   1   8   10795.00    2750.00
0   1   8   25585.00    14105.00
0   1   6   7560.00 2300.00

However, the first row should have outputed Carol Viescas in the Name column... Why is it that we get a zero instead?

CodePudding user response：

The operator || is the Logical OR operator in MySql (deprecated since version 8.0.17) and not the concatenation operator.

So when you use it with strings as operands MySql does an implicit conversion of the strings to numbers (check Type Conversion in Expression Evaluation for details) which has 0 as a result for any string that does not start with a number and the final result is 0 (= false).
The result could also be 1 (= true) if any string starts with a non zero numeric part, just like (I suspect) is the case with the column CustZipCode and you get 1 for the "Address".

If you want to concatenate strings you should use the function CONCAT():

select CONCAT(customers.CustFirstName, customers.CustLastName) as "Name", 
       CONCAT(customers.CustStreetAddress, customers.CustZipCode, customers.CustState) as "Address",
       ......................................