#1054 - Unknown column in 'group statement'

Question

I have this Sql query

SELECT tt.ID_Partenaire, tt.Nom_Societe,tt.Image_Societe, tt.id_activite 
                FROM affiliate AS a 
                RIGHT JOIN 
                  (SELECT p.ID_Partenaire, p.Nom_Societe, p.Image_Societe,p.id_activite,
                        IFNULL(SUM(c.montant_real),0) AS mt1 
                    FROM partenaires as p 
                        LEFT JOIN commande AS c 
                    ON p.ID_Partenaire=c.id_partenaire 
                    GROUP BY P.ID_Partenaire 
                  )AS tt 
            ON a.id_partenaire= tt.ID_Partenaire 
            GROUP BY tt.ID_Partenaire 
            ORDER BY (IFNULL(SUM(a.montat),0) + mt1) DESC 
            LIMIT 20

I have two server :

1. Version du serveur : 5.7.11-log - MySQL Community Server (GPL) -locally-
2. Server version: 10.1.25-MariaDB-1~jessie - mariadb.org binary distribution - host -

When i execute it on server1 it gives me a correct result but on server2 it gives me the error #1054 - Unknown column 'P.ID_Partenaire' in 'group statement'

---

the solution is the lower case P in group

---

I have another problem is that the results are not the same between the servers even that they have the same DB

Answer 1

The problem is the use of p as alias to partenaires table and the use of P (upper case) in the group statement (P.ID_Partenaire).

Try to standardize and not mix upper and lower case.

Also, have in mind that the SQL commands vary a bit from a DBMS to another...