Query in mysql does not work

Asked

Viewed 44 times

0

I need to present 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’s giving a group by error, how would this group by?? tried in several ways but it didn’t work.

mysql

  • What a mistake you’re making?

  • he says that ta missing a grouper term but I tried coloka group by e dai da pau in the table

2 answers

0

got it 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

0

How are you using clauses like COUNT e SUM, necessarily need to define a group for this.

For your consultation, I believe you’d like to see USUARIO, then I’d be 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

In addition, you may need to use the HAVING COUNT:

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

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.