Query in mysql does not work

Question

Query in mysql does not work

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I need to display a report and my query is this:

SELECT 
f.nome, 
count(hv.id) as qtdvendas,
sum(hv.total) as totalvendido,
sum(iv.valor_venda - iv.valor_compra) as lucrototal,
ROUND(sum(iv.valor_venda - iv.valor_compra)/count(hv.id), 2) as lucromedio,
ROUND(sum(hv.total) * c.comissao / 100, 2) comissao,
IF(c.meta>=sum(hv.total),ROUND(c.meta * c.premio / 100, 2),0.00) as premio,
ROUND(sum(hv.total) * 100 /c.meta, 2) as percmeta,
ROUND((sum(hv.total) * c.comissao / 100 ) + (c.meta * c.premio / 100), 2) as comprem 
from usuarios f 
left join historico_venda hv on hv.id_usuario = f.id
left join itens_venda iv on iv.id_venda = hv.id
left join comissao c on c.id_usuario = f.id
where 
c.mes='201801'
AND hv.dia >= '20180101' 
AND hv.dia <= '20180131'
AND f.acesso='1' AND f.ativo='1' AND f.id_grupo='1'
order by totalvendido DESC, percmeta DESC

he is giving a group by error, what would this group by ?? I tried it in many ways but it did not work.

mysql

asked by anonymous 27.01.2018 / 19:16

2 answers

Access the link and it instead of opening the site downloads an httpd strcasecmp Does not work as expected

score 0 · Answer 1

I got this way

SELECT f.nome, SUM(hv.total) as totalvendido, count(hv.id) as qtdvendas, sum(hv.total) as totalvendido, sum(iv.valor_venda - iv.valor_compra) as lucrototal, ROUND(sum(iv.valor_venda - iv.valor_compra)/count(hv.id), 2) as lucromedio, ROUND(sum(hv.total) * c.comissao / 100, 2) as comissao, IF(c.meta<=sum(hv.total),ROUND(c.meta * c.premio / 100,2),0) as premio, ROUND(sum(hv.total) * 100 /c.meta, 2) as percmeta, ROUND(sum(hv.total) * c.comissao / 100, 2) + IF(c.meta<=sum(hv.total),ROUND(c.meta * c.premio / 100,2),0) as comprem from usuarios f left join historico_venda hv on hv.id_usuario = f.id left join itens_venda iv on iv.id_venda = hv.id left join comissao c on c.id_usuario = f.id where c.mes='201801' AND hv.dia >= '20180101' AND hv.dia <= '20180131' AND f.acesso='1' AND f.ativo='1' AND f.id_grupo='2' group by f.nome,c.comissao,c.meta,c.premio order by totalvendido DESC

score 0 · Answer 2

Since you are using clauses such as COUNT e SUM , you must necessarily define a group for that.

By your query, I believe you want to see USUARIO , so I'd stay out of this:

SELECT 
f.nome, 
count(hv.id) AS qtdvendas,
sum(hv.total) AS totalvendido,
sum(iv.valor_venda - iv.valor_compra) AS lucrototal,
ROUND(sum(iv.valor_venda - iv.valor_compra)/count(hv.id), 2) AS lucromedio,
ROUND(sum(hv.total) * c.comissao / 100, 2) comissao,
IF(c.meta>=sum(hv.total),ROUND(c.meta * c.premio / 100, 2),0.00) AS premio,
ROUND(sum(hv.total) * 100 /c.meta, 2) AS percmeta,
ROUND((sum(hv.total) * c.comissao / 100 ) + (c.meta * c.premio / 100), 2) AS comprem 
FROM usuarios f 
LEFT JOIN historico_venda hv ON hv.id_usuario = f.id
LEFT JOIN itens_venda iv ON iv.id_venda = hv.id
LEFT JOIN comissao c ON c.id_usuario = f.id
WHERE c.mes='201801'
AND hv.dia BETWEEN '20180101' AND '20180131'
AND f.acesso='1' AND f.ativo='1' AND f.id_grupo='1'
GROUP BY f.nome
ORDER BY totalvendido DESC, percmeta DESC

Additionally, you may have to use HAVING COUNT :

GROUP BY f.nome
HAVING COUNT totalvendido > 1000
ORDER BY totalvendido DESC, percmeta DESC