hi all

i'm trying to make a simple COUNT in SQL, but i always get an exception.
here is the problematic part with it's SQL in my procedure :
{code}
  S:=QuotedStr(IWDBLCombo1.SelectedValue);

  With ibqProblems Do Begin
    If Active Then Close;
    SQL.Text:='SELECT "PProblem", COUNT("PProblem") AS Totals'+
              ' FROM "Problems"';
              ' WHERE "PType"='+S+
              ' GROUP BY "PProblem"'+
              ' ORDER BY "PProblem"';
    Open;
  End;
{code}

the error i get is :
Dynamic SQL Error
SQL error code = -104
Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause)

even when i try different variations of the SQL.

i just want to get a list of the problems, and the number of times each problem occured.

i use Firebird 2.5, IBX components that came with Delphi XE2 and intraweb(if that matters)

(P.S.  i know the IBX is not suppose to support the firebird db)

Thanks!

Edited by: Eitan Arbel on Jun 22, 2014 11:27 AM

> {quote:title=Eitan Arbel wrote:}{quote}
> hi all
> 
> i'm trying to make a simple COUNT in SQL, but i always get an exception.
> here is the problematic part with it's SQL in my procedure :
> {code}
>   S:=QuotedStr(IWDBLCombo1.SelectedValue);
> 
>   With ibqProblems Do Begin
>     If Active Then Close;
>     SQL.Text:='SELECT "PProblem", COUNT("PProblem") AS Totals'+
>               ' FROM "Problems"';
>               ' WHERE "PType"='+S+
>               ' GROUP BY "PProblem"'+
>               ' ORDER BY "PProblem"';
>     Open;
>   End;
> {code}
> 
> the error i get is :
> Dynamic SQL Error
> SQL error code = -104
> Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause)
> 
> even when i try different variations of the SQL.
> 
> i just want to get a list of the problems, and the number of times each problem occured.
> 
> i use Firebird 2.5, IBX components that came with Delphi XE2 and intraweb(if that matters)
> 
> (P.S.  i know the IBX is not suppose to support the firebird db)
> 
> Thanks!
> 
Whats wrong with

{code}     
  with ibqProblems do 
    begin
      If Active Then 
        Close;
     SQL.Text := 'SELECT PProblem, COUNT(PProblem) AS Totals'+
                         ' FROM Problems ' +
                         ' WHERE PType = :PType '
                         ' GROUP BY PProblem ' +
                         ' ORDER BY PProblem';
     ParamByName( 'PType' ).AsString := IWDBLCombo1.SelectedValue
    Open;
  End;
{code}


NB I use FireDAC ... don't see the need for the double quotes

--
Linden
"Mango" was Cool but "Wasabi" was Hotter but remember it's all in the "source"

you'r right about the ParamByName.
i've tested so many things to make it work and forgot to set it back again.

as for the double quotes, someone once explained to me why i need to do it, but i forgot why now...  lol
i use XE2 and don't have FireDAC

yes, the S is a string.

but i don't think it's the quotes.
i still get this error :
Dynamic SQL Error
SQL error code = -104
Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause)

even when the SQL is only this :
{code}
SELECT "PProblem", COUNT("PProblem") AS Totals
FROM "Problems"
{code}

so if i understand it correctly, it looks to me like it doesn't want me to use 2 fields that gives a different number of row answers in the same SELECT.
what i mean is that if i use SELECT "PProblem", then it will give me more then one row of an answer,
and if i use SELECT COUNT("PProblem"), then the answer will be only 1 "cell" of an answer.

i'm sure the problem is not in the IBX, because i get the error also when i work with IBExpert, and Firebird Maestro.
so like you said, it's probably something with the syntax, that the SQL engine doesn't "like"...

any idea how to make this SQL work properly please?

Thanks!