Subject Re: [firebird-support] SELECT GROUP BY
Author livius
Post date 2017-12-26T15:51:15Z
Hi,
 
if you need only one record then why do you use GROUP BY?
GROUP BY divide your resultset into defined groups.
Remove whole GROUP BY and acquire sum from whole records as 1 record result
 
 
regards,
Karol Bieniaszewski
 
Sent: Monday, December 25, 2017 9:06 PM
Subject: [firebird-support] SELECT GROUP BY
 
 

Hi all,


SELECT

CAST( COALESCE(CL.QUANTITE * CL.PRIX_UNITAIRE,0) AS NUMERIC(9,2)) AS TOTAL_HT,

SUM(CAST(CL.QUANTITE * CL.PRIX_UNITAIRE *( COALESCE(CL.TAUX_REMISE,0)/100.0) AS NUMERIC(9,2))) AS TOTAL_REMISE, SUM((CL.QUANTITE * CL.PRIX_UNITAIRE) - CL.QUANTITE * CL.PRIX_UNITAIRE * (COALESCE(CL.TAUX_REMISE,0)/100.0)) AS TOTAL_HT_NET,

SUM((COALESCE(CL.TAUX_TVA,0)/100.0)*CL.QUANTITE * CL.PRIX_UNITAIRE*(1.0-(COALESCE(CL.TAUX_REMISE,0)/100.0))) AS TOTAL_TVA,

SUM(CL.QUANTITE * CL.PRIX_UNITAIRE*(1.0-COALESCE(CL.TAUX_REMISE,0)/100) *(1+COALESCE(CL.TAUX_TVA,0)/100.0)) AS TOTAL_TTC

 

FROM COMMANDE_FOUR_LIGNE CL WHERE CL.COMMANDE_ID=1

 

GROUP BY

CL.QUANTITE,CL.PRIX_UNITAIRE,CL.TAUX_REMISE,CL.TAUX_TVA,CL.COMMANDE_ID



I need to sum records which have the same foreig n key value (CL.COMMANDE_ID=1) in order to obtain only one line of the fields I sum. The table contains 4 records and I get 4 four records instead of one record. Is that something wrong with my query,can you please help me ?

 



Avast logo

Ta wiadomość została sprawdzona na obecność wirusów przez oprogramowanie antywirusowe Avast.
www.avast.com