Board index » delphi » Using GROUP BY in local SQL

Using GROUP BY in local SQL

Hello everyone,

I'm using Delphi 1.0 and TQuery's SQL statements to access local
Paradox tables. When executing the following SQL statement:

Select item_nr, SUM(item_price),order_date
FROM ITEMS
WHERE (item_nr=1) OR (item_nr=2)
GROUP BY order_date

Delphi's latest BDE complains 'capability not supported'.

However, if the OR-part is left out (that is OR items_nr=2)
the query works fine.

Is is really so that OR-clauses and GROUP BY can't be used together?

Any ideas or work-arounds would be much appreciated.

Thanks a million,

Sami El-Mahgary

Helsinki University of Technology
Systems Analysis Lab
02150 Espoo, Finland

email: mahg...@niksula.cs.hut.fi

 

Re:Using GROUP BY in local SQL


Quote
Sami J El-Mahgary wrote:

> Hello everyone,

> I'm using Delphi 1.0 and TQuery's SQL statements to access local
> Paradox tables. When executing the following SQL statement:

> Select item_nr, SUM(item_price),order_date
> FROM ITEMS
> WHERE (item_nr=1) OR (item_nr=2)
> GROUP BY order_date

> Delphi's latest BDE complains 'capability not supported'.

> However, if the OR-part is left out (that is OR items_nr=2)
> the query works fine.

> Is is really so that OR-clauses and GROUP BY can't be used together?

> Any ideas or work-arounds would be much appreciated.

> Thanks a million,

> Sami El-Mahgary

> Helsinki University of Technology
> Systems Analysis Lab
> 02150 Espoo, Finland

> email: mahg...@niksula.cs.hut.fi

Just a guess, but try inclosing all group by elements in parentheses as
GROUP BY ((item_nr=1) OR (item_nr=2)).  This is the way the Query
Builder does it.

Re:Using GROUP BY in local SQL


In article <5f3idu$...@nntp.hut.fi>, selma...@cc.hut.fi (Sami J El-Mahgary)
wrote:

Quote
>Hello everyone,

>I'm using Delphi 1.0 and TQuery's SQL statements to access local
>Paradox tables. When executing the following SQL statement:

>Select item_nr, SUM(item_price),order_date
>FROM ITEMS
>WHERE (item_nr=1) OR (item_nr=2)
>GROUP BY order_date

>Delphi's latest BDE complains 'capability not supported'.

>However, if the OR-part is left out (that is OR items_nr=2)
>the query works fine.

>Is is really so that OR-clauses and GROUP BY can't be used together?

>Any ideas or work-arounds would be much appreciated.

>Thanks a million,

>Sami El-Mahgary

>Helsinki University of Technology
>Systems Analysis Lab
>02150 Espoo, Finland

>email: mahg...@niksula.cs.hut.fi

I believe that all of the non-aggregated fields need to be in the group by
clause. Try this:

Select item_nr, SUM(item_price),order_date
FROM ITEMS
WHERE (item_nr=1) OR (item_nr=2)
GROUP BY item_nr, order_date

If you don't want the query grouped by item_nr, then you should probably take
it out of the select clause.

Bob

Re:Using GROUP BY in local SQL


Quote
Sami J El-Mahgary wrote:

> Hello everyone,

> I'm using Delphi 1.0 and TQuery's SQL statements to access local
> Paradox tables. When executing the following SQL statement:

> Select item_nr, SUM(item_price),order_date
> FROM ITEMS
> WHERE (item_nr=1) OR (item_nr=2)
> GROUP BY order_date

> Delphi's latest BDE complains 'capability not supported'.

> However, if the OR-part is left out (that is OR items_nr=2)
> the query works fine.

> Is is really so that OR-clauses and GROUP BY can't be used together?

> Any ideas or work-arounds would be much appreciated.

> Thanks a million,

> Sami El-Mahgary

> Helsinki University of Technology
> Systems Analysis Lab
> 02150 Espoo, Finland

> email: mahg...@niksula.cs.hut.fi

Sami,

Any fields in the SELECT clause must be part of an aggregate function or
the GROUP BY clause.  It does not make sense to SUM over price and try
to select item_nr; the SQL engine does not know which item_nr to pick!
I do not understand semantically what you are trying to accomplish, but
syntactically speaking try:

   Select item_nr, SUM(item_price), order_date
   FROM ITEMS
   WHERE (item_nr=1) OR (item_nr=2)
   GROUP BY item_nr, order_date

Dan Star
dans...@execpc.com

Other Threads