0
How can I catch a field I want doing the 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';
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.
2
Put fields that are not aggregation in one 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 turn non-aggregation fields into aggregation fields (max, min, avg.. ) (I don’t 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';
-1
Buddy, like the log that you posted you need to group and for thatgroup 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 remark, how you are grouping to perform recover from count
for PVN.Nfnum it is not necessary to use the distinct, will only cause your query to perform worse.
Browser other questions tagged mysql sql
You are not signed in. Login or sign up in order to post.
You had created a similar topic and the answers from that topic considered what you had reported there. But now created another topic, changing the question a little. The suggestion is that, when necessary, add information to the existing topic, instead of creating a new topic as you recall a new detail that you had not previously reported.
– José Diz