Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: simple SQL COUNT doesn't work


This question is answered.


Permlink Replies: 12 - Last Post: Jun 24, 2014 2:49 AM Last Post By: Eitan Arbel
Eitan Arbel

Posts: 508
Registered: 2/24/13
simple SQL COUNT doesn't work  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jun 22, 2014 12:52 AM
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 :
  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;


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
Linden ROTH

Posts: 467
Registered: 11/3/11
Re: simple SQL COUNT doesn't work  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jun 22, 2014 1:54 AM   in response to: Eitan Arbel in response to: Eitan Arbel
Eitan Arbel wrote:
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 :
  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;


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

     
  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;

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"

Eitan Arbel

Posts: 508
Registered: 2/24/13
Re: simple SQL COUNT doesn't work  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jun 22, 2014 3:04 AM   in response to: Linden ROTH in response to: Linden ROTH
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
Linden ROTH

Posts: 467
Registered: 11/3/11
Re: simple SQL COUNT doesn't work  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jun 22, 2014 3:01 PM   in response to: Eitan Arbel in response to: Eitan Arbel
Eitan Arbel wrote:
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

But did you try WITHOUT double quotes because for me it fails because of them

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

Posts: 1,529
Registered: 9/23/99
Re: simple SQL COUNT doesn't work
Helpful
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jun 22, 2014 6:47 PM   in response to: Eitan Arbel in response to: Eitan Arbel
Eitan Arbel wrote:
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

Double quotes are used for quoted identifiers. Mainly it makes the column name
case sensitive.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
And so I patrol in the valley of the shadow of the tricolor
I must fear evil. For I am but mortal and mortals can only die.
Asking questions, pleading answers from the nameless
faceless watchers that stalk the carpeted corridors of Whitehall.
(Fish)
Linden ROTH

Posts: 467
Registered: 11/3/11
Re: simple SQL COUNT doesn't work  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jun 22, 2014 8:24 PM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
Jeff Overcash (TeamB) wrote:

Double quotes are used for quoted identifiers. Mainly it makes the column name
case sensitive.

--
Jeff Overcash (TeamB)

Uhhh like C, C++ and C#

I'm still Linden & LINDEN and LiNdEn - case sensitive an abomination and EVIL but that's just my opinion

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

Posts: 1,529
Registered: 9/23/99
Re: simple SQL COUNT doesn't work [Edit]
Helpful
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jun 22, 2014 10:01 PM   in response to: Eitan Arbel in response to: Eitan Arbel
Eitan Arbel wrote:
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 :
  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;


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

Only thing not shown here is S. Is S a string? If so you need to quote it
(single quotes, not double quotes). IBX does not change your SQL and that is a
server side error so your SQL in the end is not valid. Your double quoting of
the columns is probably right and they are case sensitive or else you normally
would get an error about column not found before a deeper error about the group by.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
And so I patrol in the valley of the shadow of the tricolor
I must fear evil. For I am but mortal and mortals can only die.
Asking questions, pleading answers from the nameless
faceless watchers that stalk the carpeted corridors of Whitehall.
(Fish)
Eitan Arbel

Posts: 508
Registered: 2/24/13
Re: simple SQL COUNT doesn't work [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jun 23, 2014 4:59 AM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
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 :
SELECT "PProblem", COUNT("PProblem") AS Totals
FROM "Problems"


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!
quinn wildman

Posts: 856
Registered: 12/2/99
Re: simple SQL COUNT doesn't work [Edit]
Correct
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jun 23, 2014 8:30 AM   in response to: Eitan Arbel in response to: Eitan Arbel
Eitan Arbel wrote:
SELECT "PProblem", COUNT("PProblem") AS Totals
FROM "Problems"

This is not legal SQL. You need a group by whenever you have aggregate
function. Try:

SELECT "PProblem", COUNT("PProblem") AS Totals
FROM "Problems" group by "PProblem"
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: simple SQL COUNT doesn't work [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jun 23, 2014 9:21 AM   in response to: Eitan Arbel in response to: Eitan Arbel
Eitan Arbel wrote:
yes, the S is a string.

Without quotes around the value of S, the SQL engine will think that you are
comparing it to another column, not to a string literal like you want.

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 :
SELECT "PProblem", COUNT("PProblem") AS Totals
FROM "Problems"


This is missing a group by. When you have an aggregate field, in this case a
count, all non aggregate fields must be included in the group by clause.

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!

SELECT "PProblem", COUNT("PProblem") AS Totals
FROM "Problems"
group by "Problems"

should work just fine.


--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
And so I patrol in the valley of the shadow of the tricolor
I must fear evil. For I am but mortal and mortals can only die.
Asking questions, pleading answers from the nameless
faceless watchers that stalk the carpeted corridors of Whitehall.
(Fish)

Eitan Arbel

Posts: 508
Registered: 2/24/13
Re: simple SQL COUNT doesn't work [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jun 24, 2014 2:35 AM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
SELECT "PProblem", COUNT("PProblem") AS Totals
FROM "Problems"


This is missing a group by. When you have an aggregate field, in this case a
count, all non aggregate fields must be included in the group by clause.
finally! - a clean and simple explanation.. :)
i tried to understand this aggregated thing, and read about it in several sites, but never really understood what it means (sorry English is not my main language...) .



SELECT "PProblem", COUNT("PProblem") AS Totals
FROM "Problems"
group by "Problems"

should work just fine.

this gives another error :
Dynamic SQL Error
SQL error code = -206
Column unknown
Problems
At line 1, column 70

i think you meant :
group by "PProblem"

Thanks!

Edited by: Eitan Arbel on Jun 24, 2014 12:36 PM
Eitan Arbel

Posts: 508
Registered: 2/24/13
Re: simple SQL COUNT doesn't work  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jun 24, 2014 2:09 AM   in response to: Eitan Arbel in response to: Eitan Arbel
Thank you VERY VERY MUCH guys!

here is the final code and query i needed it to be :
  With ibqProblems Do Begin
    If Active Then Close;
 
    SQL.Text:='SELECT "PProblem", COUNT(*) AS Totals'+
              ' FROM "Problems"'+
              ' WHERE "PType"=:PType'+
              ' GROUP BY "PProblem"'+
              ' ORDER BY "PProblem" ASC';
    ParamByName('PType').AsString:=IWDBLCombo1.SelectedValue;
 
    Open;
  End;//With ibqProblems


again, thank you VERY MUCH for your great help guys!
Eitan Arbel

Posts: 508
Registered: 2/24/13
Re: simple SQL COUNT doesn't work  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jun 24, 2014 2:49 AM   in response to: Eitan Arbel in response to: Eitan Arbel
ouch...
i didn't know i can mark only 2 "Helpful", and 1 "correct" answers...
sorry...

MANY thanks for your patience and great help !
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02