How to use Max() and Count() function together?

Asked

Viewed 679 times

0

Talk personal! I am training a subselect with having using the MAX AND COUNT functions together, but Sqlserver is returning me an error called: "It is not possible to perform an aggregation function on an expression that contains an aggregation or sub-allowance."

I usually do the scripts in oracle sql Developer I don’t know if it’s some syntax error, can anyone help me? Below is the command executed:

select clfDescricao
from classificacaofiscal c, notafiscaleletronica n
where c.clfCFO = n.NFE_cnatOP
group by clfDescricao
having count(n.NFE_id) = (select max(count(n.NFE_id))
from classificacaofiscal c, notafiscaleletronica n
where c.clfCFO = n.NFE_cnatOP)
mysql sql oracle

  • Try to explain what you want to get.

  • Which DB engine? You mention both SQL Server and Oracle. To facilitate playback of the problem, try creating a fiddle at http://sqlfiddle.com/

  • And as said before, explain what you want to get. Try not to focus on how you’re doing it. The error may be in the form of implementing what is desired. It does not seem to make sense to use select max(count(n.NFE_id)), for example.

  • Testing: select count(n.NFE_id)
from classificacaofiscal c, notafiscaleletronica n
where c.clfCFO = n.NFE_cnatOP with your data and evaluate the result. Then check if it makes sense to use the max function in this select.

  • Folks I’m doing the script on Oracle, I’m trying to answer the following question: ?

  • A solution would be to make a Count by grouping by CFOP, sort in descending order and catch only the first. In this case you may not get the complete information if there are more than one CFOP with the same amount. Another solution would be to take the Count in a subselect and the outermost select take the max over the subselect result.

Show 1 more comment

1 answer

0

I read from the comments that you’re trying to do this on Oracle. You can do it this way:

/*Cria o agrupamento por descrição*/
WITH dados AS (
SELECT clfdescricao, 
       MAX(COUNT(n.nfe_id)) cnt
  FROM classificacaofiscal c, notafiscaleletronica n
 WHERE c.clfcfo = n.nfe_cnatop
 GROUP BY clfdescricao)

/*Retorna apenas o que tiver maior número de notas*/
SELECT clfdescricao 
  FROM dados 
 WHERE cnt = (SELECT MAX(cnt) FROM dados)

At first, you lose a little performance, but it makes the code easier to understand. So, it is valid to make an evaluation of the volume of data, to see what will be the impact.

In your case, basically you just forgot to put the group by within your sub-select, as there is no connection with the query from the outside, you could run it separately and check why it didn’t work.

Follow the example using the function HAVING:

SELECT clfdescricao
  FROM classificacaofiscal c, notafiscaleletronica n
 WHERE c.clfcfo = n.nfe_cnatop
 GROUP BY clfdescricao
HAVING COUNT(n.nfe_id) = (SELECT MAX(COUNT(n.nfe_id))
                            FROM classificacaofiscal  c,
                                 notafiscaleletronica n
                           WHERE c.clfcfo = n.nfe_cnatop
                           GROUP BY clfdescricao)

Browser other questions tagged mysql sql oracle

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