How can I get a field I want to make COUNT work?
select PVN.NFNum, COUNT(distinct CtrlCargaNum) as Qtd from CTRL_CARGA cc
INNER JOIN PED_VENDA_NOTA_FISCAL PVN ON cc.EmpCod = PVN.EmpCod
where cc.CtrlCargaData between '2018-01-01' and '2018-01-04' and cc.EmpCod = '01.01';
It gives the following error in MySQL Server :
Column 'PED_VENDA_NOTA_FISCAL.NFNum' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Put the non-aggregate fields in group by :
select PVN.NFNum,
COUNT(distinct CtrlCargaNum) as Qtd from CTRL_CARGA cc
INNER JOIN PED_VENDA_NOTA_FISCAL PVN ON cc.EmpCod = PVN.EmpCod
where cc.CtrlCargaData between '2018-01-01' and '2018-01-04'
and cc.EmpCod = '01.01'
Group by PVN.NFNum;
Or transform non-aggregate fields into aggregate fields (max, min, avg ..) (I do not recommend):
select Max(PVN.NFNum) as NFNum,
COUNT(distinct CtrlCargaNum) as Qtd from CTRL_CARGA cc
INNER JOIN PED_VENDA_NOTA_FISCAL PVN ON cc.EmpCod = PVN.EmpCod
where cc.CtrlCargaData between '2018-01-01' and '2018-01-04'
and cc.EmpCod = '01.01';
Friend, like the log that you posted you need to group and for that group by .
select PVN.NFNum, COUNT(distinct CtrlCargaNum) as Qtd from CTRL_CARGA cc INNER JOIN PED_VENDA_NOTA_FISCAL PVN ON cc.EmpCod = PVN.EmpCod where cc.CtrlCargaData between '2018-01-01' and '2018-01-04' and cc.EmpCod = '01.01' Group by PVN.NFNum
Another note, how you are grouping to accomplish recovering from count
by PVN.NFNum you do not need to use distinct , it will only make your query perform worse.