Sql incorrect syntax

Question

select max(qtd) 
from (select count(int_re_usu) as qtd from tb_questionario_voar_resposta)

Why this query dont work? I want to retrieve the max value from all count

it says incorrect syntax near')'

Any ideias?

The query itself doesn't make sense. The subquery will always return one row (the total number of lines in your table), therefore selecting the maximum out of one row will always return this row... — Vincent Savard
– Vincent Savard, Commented Nov 16, 2010 at 16:25
I think you may be missing a GROUP BY clause in the sub-query. The query as written is the same as the sub-query "select count(int_re_usu) as qtd from tb_questionario_voar_resposta" — Larry Lustig
– Larry Lustig, Commented Nov 16, 2010 at 16:27
Like Vincent said - this doesn't make much sense - what are you trying to accomplish by using a sub query? — Christoph
– Christoph, Commented Nov 16, 2010 at 16:27

D'Arcy Rittich · Accepted Answer · 2010-11-16 16:24:20Z

3

You need an alias on a derived table:

select max(qtd) 
from (
    select count(int_re_usu) as qtd 
    from tb_questionario_voar_resposta
) a

But as Vincent pointed out, that just returns one row, and I think you are missing a group by. You can really just do:

    select max(count(int_re_usu)) as qtd 
    from tb_questionario_voar_resposta
    group by SomeColumn

answered Nov 16, 2010 at 16:24

D'Arcy Rittich

172k41 gold badges298 silver badges287 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vincent Savard Over a year ago

Depending on the RDBMS, you may not be able to use nested aggregate functions. But anyway, selecting the max out of a count is useless (without GROUP BY).

ozsenegal Over a year ago

no it says:Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Vishal · Accepted Answer · 2010-11-16 16:34:40Z

0

Try this you are missing a group by I think and use an alias-

     select 
              max(d1.qtd) 
        from 
        (
          select 
                count(int_re_usu) as qtd 
          from tb_questionario_voar_resposta
        group by qtd
        ) as d1
group by d1.qtd

edited Nov 16, 2010 at 16:34

answered Nov 16, 2010 at 16:26

Vishal

12.4k20 gold badges85 silver badges129 bronze badges

2 Comments

Vincent Savard Over a year ago

You want to put the group by inside the subquery (or just remove it). But since OP didn't say anything about it, it's hard to know how to use it.

Vishal Over a year ago

yea ur right..fixed it thanks.I am just trying to suggest it I am not sure what OP wants..

Collectives™ on Stack Overflow

Sql incorrect syntax

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related