sum grandtotal per code article (ciusmd)

[hugoscp1]

this is my query:
select NFREFC, nmff01, ciusmd, nmismd, sum(QRCEGP) as qtd
from swapfilio.gcffc join swapfilio.gcffp
on SOCEFC = SOCEFP
and NFREFC = FRNEFP
and NRFEFC = NRFEFP
join swapfilio.gcfgp
on SOCEGP = SOCEFP
and ARMEGP = ARMEFP
and AGREGP = AGREFP
and NGREGP = NGREFP
and NLNEGP = NLNEFP
and ARTEGP = ARTEFP
and MODEGP = MODEFP
and EMBEGP = EMBEFP
and GFMEGP = GFMEFP
join swapfilio.gesmd
on socsmd = SOCEGP
and niismd = ARTEGP
left join swapfilio.for01
on NFOF01 = NFREFC
and SOCF01 = SOCEFC
where SOCF01 = '1'
and SOCEFC = '1'
and socsmd = '1'
and (ciusmd = '5179627' or ciusmd = '6191569')
and DFCEFC >= 20180801
group by NFREFC, nmff01, ciusmd, nmismd

Result:

NFREFC NMFF01 CIUSMD NMISMD QTD
802 OCP PORTUGAL - PROD FARM, SA 6191569 ACCU-CHECK SAFE-T-PRO UNO LANCETAS 2.000
801 BOTELHO & RODRIGUES LDA 6191569 ACCU-CHECK SAFE-T-PRO UNO LANCETAS 2.000
803 COOPROFAR - COOP PROPRIET FARM CRL 6191569 ACCU-CHECK SAFE-T-PRO UNO LANCETAS 1.000
1825 GFPH-PHARMA,LDA. 5179627 ASACOL 800 MG COMP.GR X60 160.000
802 OCP PORTUGAL - PROD FARM, SA 5179627 ASACOL 800 MG COMP.GR X60 92.000
804 UDIFAR II DISTRI FARMACÊUTICA SA 5179627 ASACOL 800 MG COMP.GR X60 1.000
803 COOPROFAR - COOP PROPRIET FARM CRL 5179627 ASACOL 800 MG COMP.GR X60 11.000

What i want:

NFREFC NMFF01 CIUSMD NMISMD QTD
802 OCP PORTUGAL - PROD FARM, SA 6191569 ACCU-CHECK SAFE-T-PRO UNO LANCETAS 2.000 5
801 BOTELHO & RODRIGUES LDA 6191569 ACCU-CHECK SAFE-T-PRO UNO LANCETAS 2.000 5
803 COOPROFAR - COOP PROPRIET FARM CRL 6191569 ACCU-CHECK SAFE-T-PRO UNO LANCETAS 1.000 5
1825 GFPH-PHARMA,LDA. 5179627 ASACOL 800 MG COMP.GR X60 160.000 264
802 OCP PORTUGAL - PROD FARM, SA 5179627 ASACOL 800 MG COMP.GR X60 92.000 264
804 UDIFAR II DISTRI FARMACÊUTICA SA 5179627 ASACOL 800 MG COMP.GR X60 1.000 264
803 COOPROFAR - COOP PROPRIET FARM CRL 5179627 ASACOL 800 MG COMP.GR X60 11.000 264

I dont now what to get the expected result above, perhaps a subquery?
Please help me

Thank you

 

[fredericofonseca]

and what is the last column for? what does it mean, how do you calculate it, and based on what?

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[hugoscp1]

the last column is what i want to calculate.
Its the sum (QTD) of the article (ciusmd)
Example:
This article 6191569 (ciusmd) as this quantatys (QTD) 2 +2 +1 = 5 (i want the sum 5 in this example in all 6191569 lines)
and the article 5179627 as 160 + 92 + 1 + 11 (QTD) = 264 (i want the sum 264 in this example in all 5179627 lines)

Thank you

 

[fredericofonseca]

without sample data and desired output we can't be sure of how to do it - but if based on the same sql given adding a simple "count(*)" to the select may be the solution.

If not post sample data that shows how all of your query works and the desired output.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[hugoscp1]

acho que és portugues, vou escrever em portuguès, para quê o count quando eu preciso é do sumatório

 

[fredericofonseca]

count or sum - whatever is required to get your results - and as you didn't specify the details of how to get the values e.g. input data, calculation (your sql) and final output, we cant be sure what is the correct value - your sql isn't helping either as it does not use alias on the tables and therefore its not possible to determine where the fields are from - 5 tables - which table is the quantity coming from? and will there be multiple records with the same quantity and you only need one of them? too many questions and not enough data to go through it.


And this being an english forum keeping it in english it better for those that may wish to help, as well as those looking for a solution to a similar problem.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886