Select with 3 tables

Hi Jaber,

From tb_shop_FA F, tb_shop_recap r

should be something like

from tb_shop_FA f
inner join tb_shop_recap r
    on r.SomeID = f.SomeID
...

otherwise you have a cartesian or square join - that is, every row of f matches with every row of r,

specifically, the error message says that Libelle_TAG isn't a column in table tb_shop_FA

HTH
  David

Thank you David Todd for your help, it worked well.
I ask again for your help with the GROUP BY function

 GROUP BY
F.Reference,
F.Libelle_TAG,
F.Quantite,
F.Prix_Unitaire,
F.Prix_Total,
F.Remise,
F.Total,
R.Reference,
R.Coupon,
R.Etat_Commande,
P.Libelle_FR,
P.Libelle_TAG,
P.Contenance_FR 

Select allOpen in new window

When I withdraw this code, the list is ordered and of course not grouped.

When I add it, the list is mixed ... but not serious for now. As soon as I delete a line, I get this error:
SQLSTATE [42000]: syntax error or access violation: 1055 Expression n ° 1 of the SELECT list is not in the GROUP BY clause and contains non-aggregated
column 'sivop_dbase.F.Reference' which does not depend functionally on the columns of the GROUP BY clause; it's incompatible with
sql_mode = only_full_group_by

I very often get this error and I don't understand the reason and the real operation of the GROUP BY function.

I tried to delete everything that may not be grouped and leave those who can be grouped, but I still encounter this error.

The objective is to group:

F. Reference, (single value)
R.Reference, (single value)

F.Libelle_TAG, (single value)
P.Libelle_TAG, (single value)

F. Quantity, (COUNT)
F. Unit_Price, (SUMM)
F. Price_Total, (SUMM)
F. Discount, (single value of "15%")
F. Total, (SUMM)

R.Status_Order, (Single value "Preparation")
P.Libelle_FR + P.Contenance_FR (single value)

Select allOpen in new window

Here is my final code:

SELECT
F.Reference,
F.Libelle_TAG,
F.Quantite,
F.Prix_Unitaire,
F.Prix_Total,
F.Remise,
F.Total,

R.Reference,
R.Coupon,
R.Etat_Commande,

P.Libelle_FR,
P.Libelle_TAG,
P.Contenance_FR

FROM tb_shop_FA F

INNER JOIN tb_shop_recap R ON R.Reference = F.Reference
LEFT JOIN tb_produits P ON P.Libelle_TAG = F.Libelle_TAG

WHERE R.Coupon <> '0' AND R.Etat_Commande = 'PREPARATION'

Select allOpen in new window

You can see the result here : http://www.sivop.com/admin/boutique/siv/invoice.php

Hi,

Pardon a little reformat, but give this a try:

SELECT
	F.Reference
	, F.Libelle_TAG
	, count( F.Quantite )
	, sum( F.Prix_Unitaire )
	, sum( F.Prix_Total )
	, F.Remise
	, sum( F.Total )
	
	, R.Reference
	, R.Coupon
	, R.Etat_Commande
	
	, P.Libelle_FR
	, P.Libelle_TAG
	, P.Contenance_FR

FROM tb_shop_FA F

INNER JOIN tb_shop_recap R ON R.Reference = F.Reference
LEFT JOIN tb_produits P ON P.Libelle_TAG = F.Libelle_TAG

WHERE 
	R.Coupon <> '0' 
	AND R.Etat_Commande = 'PREPARATION'

-- Group by clause
group by
	F.Reference
	, F.Libelle_TAG
	--, F.Quantite
	--, F.Prix_Unitaire
	--, F.Prix_Total
	, F.Remise
	--, F.Total
	, R.Reference
	, R.Coupon
	, R.Etat_Commande
	, P.Libelle_FR
	, P.Libelle_TAG
	, P.Contenance_FR 	

Select allOpen in new window

I think that the count of quantity should be a sum.

Anyway, the FORM of a group by is
select
    key
    , aggrigatefunction( somecolumn )
from sometable
where
    somecondition
group by
    key
;

HTH
  David

>> I don't understand the reason and the real operation of the GROUP BY function

When you use an aggregate function in  query, you need to "group" the aggregates by all columns not used in the aggregate.

A list of the aggregate functions:

https://docs.microsoft.com/en-us/sql/t-sql/functions/aggregate-functions-transact-sql?view=sql-server-ver15

So if you SUM, COUNT, ??? one or more columns, the other columns you select are what you are "grouping" by and are considered non-aggregate columns.

In your example those are  Reference and Libelle_TAG.  Your counts and sums are based on those, so you group by them.

Thanks again for your help.
I tried this code without modifying it but it returns this error to me :

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' F.Quantite --, F.Prix_Unitaire --, F.Prix_Total , F.Remise --, F.Total , R' at line 32

I think I missed something but I don't know what

SELECT 
	F.Reference 
	, F.Libelle_TAG 
	, SUM(F.Quantite) 
	, SUM(F.Prix_Unitaire) 
	, SUM(F.Prix_Total) 
	, F.Remise 
	, SUM(F.Total) 
	 
	, R.Reference 
	, R.Coupon 
	, R.Etat_Commande 
	 
	, P.Libelle_FR 
	, P.Libelle_TAG 
	, P.Contenance_FR 
 
FROM tb_shop_FA F 
 
INNER JOIN tb_shop_recap R ON R.Reference = F.Reference 
LEFT JOIN tb_produits P ON P.Libelle_TAG = F.Libelle_TAG 
 
WHERE  
	R.Coupon <> '0'  
	AND R.Etat_Commande = 'PRÉPARATION' 
 
-- Group by clause 
GROUP BY 
 
	  F.Reference 
	, F.Libelle_TAG 
	--, F.Quantite 
	--, F.Prix_Unitaire 
	--, F.Prix_Total 
	, F.Remise 
	--, F.Total 
	, R.Reference 
	, R.Coupon 
	, R.Etat_Commande 
	, P.Libelle_FR 
	, P.Libelle_TAG 
	, P.Contenance_FR

Select allOpen in new window

I tried this :

GROUP BY 
 
F.Reference 
, F.Libelle_TAG 
, F.Quantite 
, F.Prix_Unitaire 
, F.Prix_Total 
, F.Remise 
, F.Total 
 
, R.Reference 
, R.Coupon 
, R.Etat_Commande 
 
, P.Libelle_FR 
, P.Libelle_TAG 
, P.Contenance_FR

Select allOpen in new window

Here's the result

And with that :

F.Reference 
, F.Libelle_TAG 
, SUM(F.Quantite) 
, SUM(F.Prix_Unitaire) 
, SUM(F.Prix_Total) 
, F.Remise 
, SUM(F.Total) 
 
, R.Reference 
, R.Coupon 
, R.Etat_Commande 
 
, P.Libelle_FR 
, P.Libelle_TAG 
, P.Contenance_FR

Select allOpen in new window

SQLSTATE[42000]: Syntax error or access violation: 1056 Can't group on 'SUM(F.Quantite)'

>> I think I missed something but I don't know what

Based on the error message, it appears MySQL doesn't like comments in the "group by".

It is easy to reproduce.

If I try what you posted, it shows that error:

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=d3262e29ec1224d87d3bf65f4f090bba

If I remove the comments, I get the expected table not found:

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=7289210197004511d2faf72810026c3e

In the image you posted, you have SUM's in the group by.  That is wrong.

-- edited --

I just understood part of the problem, it was to add AS Quantite ....
I no longer have a PHP or SQL error, but the elements are not grouped

SELECT 
	F.Reference 
	, F.Libelle_TAG 
	, SUM(F.Quantite) AS Quantite 
	, F.Prix_Unitaire 
	, SUM(F.Prix_Total) AS Prix_Total 
	, F.Remise 
	, SUM(F.Total) AS Total 
	 
	, R.Reference 
	, R.Coupon 
	, R.Etat_Commande 
	 
	, P.Libelle_FR 
	, P.Libelle_TAG 
	, P.Contenance_FR 
 
FROM tb_shop_FA F 
 
INNER JOIN tb_shop_recap R ON R.Reference = F.Reference 
LEFT JOIN tb_produits P ON P.Libelle_TAG = F.Libelle_TAG 
 
WHERE  
	R.Coupon <> '0'  
	AND R.Etat_Commande = 'PRÉPARATION' 
 
GROUP BY  
  
  F.Reference 
, F.Libelle_TAG 
, F.Remise 
, F.Prix_Unitaire 
 
, R.Reference 
, R.Coupon 
, R.Etat_Commande 
 
, P.Libelle_FR 
, P.Libelle_TAG 
, P.Contenance_FR 
 
ORDER BY P.Libelle_FR 

Select allOpen in new window

Your image doesn't show all the columns in the select.

Look at the other columns.  Something in one or more of the non-aggregate columns will be different.

SELECT 
	F.Libelle_TAG 
	, F.Remise 
	, F.Prix_Unitaire 
	, SUM(F.Quantite) AS Quantite 
	, SUM(F.Prix_Total) AS Prix_Total 
	, SUM(F.Total) AS Total 
	 
	 
	, P.Libelle_FR 
	, P.Libelle_TAG 
	, P.Contenance_FR 
 
FROM tb_shop_FA F 
 
INNER JOIN tb_shop_recap R ON R.Reference = F.Reference 
LEFT JOIN tb_produits P ON P.Libelle_TAG = F.Libelle_TAG 
 
WHERE  
	R.Coupon <> '0'  
	AND R.Etat_Commande = 'PRÉPARATION' 
 
GROUP BY  
  
  F.Libelle_TAG 
  	, P.Libelle_FR 
	, P.Libelle_TAG 
	, P.Contenance_FR 
	, F.Remise 
	, F.Prix_Unitaire 
 
ORDER BY P.Libelle_FR 

Select allOpen in new window