GROUP BY problem 
Author Message
 GROUP BY problem

I am trying to run a SQL query against a Paradox table

select
  CellNo, RecdAmount, RecdDate, sum(RecdAmount)
from
  Rsd20
where
  CellNo=:pCellNo
group by
  CellNo

I need to calculate a total hence I need the "group by" in order to be
able to use aggregate functions. BDE is complaining that "Field in group
by must be in the result set". Has anybody any idea wht's wrong?
--
Catalin Nimitan
ph: (403) 262-0347



Wed, 18 Jun 1902 08:00:00 GMT  
 GROUP BY problem

You must include every non-calculated field in the Group By
expression.

Quote:
>select
>  CellNo, RecdAmount, RecdDate, sum(RecdAmount)
>from
>  Rsd20
>where
>  CellNo=:pCellNo
>group by

  CellNo, RecdAmount, RecdDate

I hope this helps,

EBR

On Mon, 08 Sep 1997 10:31:26 -0700, Catalin Nimitan

Quote:

>I am trying to run a SQL query against a Paradox table

>select
>  CellNo, RecdAmount, RecdDate, sum(RecdAmount)
>from
>  Rsd20
>where
>  CellNo=:pCellNo
>group by
>  CellNo

>I need to calculate a total hence I need the "group by" in order to be
>able to use aggregate functions. BDE is complaining that "Field in group
>by must be in the result set". Has anybody any idea wht's wrong?
>--
>Catalin Nimitan
>ph: (403) 262-0347




Wed, 18 Jun 1902 08:00:00 GMT  
 GROUP BY problem

U need to add all three fields in the Group By clause as shown below...

select
  CellNo, RecdAmount, RecdDate, sum(RecdAmount)
from
  Rsd20
where
  CellNo=:pCellNo
group by
  CellNo, RecdAmount, RecdDate

Sri



Wed, 18 Jun 1902 08:00:00 GMT  
 GROUP BY problem



Quote:
> select
>   CellNo, RecdAmount, RecdDate, sum(RecdAmount)
> from
>   Rsd20
> where
>   CellNo=:pCellNo
> group by
>   CellNo

> I need to calculate a total hence I need the "group by" in order to be
> able to use aggregate functions. BDE is complaining that "Field in group
> by must be in the result set". Has anybody any idea wht's wrong?

When doing a GROUP BY, all the columns in the SELECT statement must either
be an aggregate (i.e. SUM, COUNT, AVG etc.), or be part of the GROUP BY.
You cannot just simply include RecdAmount and RecdDate - you must either
add the columns to the GROUP BY clause or perform an aggregation on them.

Simply put, let's say there are 10 rows for cellphone x. You want to group
by cellnumber - so you want a single row returned from the 10 rows for
cellphone x.

You tell the db engine to add (using the SUM) the recdamount of these 10
rows together and supply you with a single result. No problem. However,
what should the db engine do with the RecdAmount and RecdDate you
requested. There are 10 rows for that cell number and thus there are 10
values each for RecdDate and RecdAmount. You requested a single row to be
returned for cell number x. Should it give you the largest date, or the
smallest? Or should it add all the dates in these 10 rows together? Or
should it give you the average date? The db engine has no idea how to
supply you a single RecdDate or RecdDate from these 10 rows for cellphone x
- you need to tell it how to take these 10 rows and from these 10 values
derive/calculate a single value for RecdDate and RecdAmount.

I think you're trying to do something like:
select
  CellNo, RecdDate, sum(RecdAmount)
from
  Rsd20
where
  CellNo=:pCellNo
group by
  CellNo,
  RecdDate

This will return the total amount per cell number per day.

regards,
Billy
PS. I suggest that you do the SQL tutorial that comes with Delphi's
Interbase. It will teach you the basics of SQL.



Wed, 18 Jun 1902 08:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Quick Report Multi-Page Grouping Problem

2. Problem with "group by" in query

3. Aggregate functions and Group By problems in Local SQL

4. GROUP BY-Problem with LIBS

5. Problem with Lotus Organizer Group Scheduler

6. group by problem

7. Anybody knows how to create multiple group in Quick Report (A group inside another group) ?

8. QuickReport. Sum by group in group footer?

9. SQL Statement month grouping

10. An alternate to GROUP BY???

11. HELP !: Local SQL and Group By

12. Obtaining a List of Users and Groups from SQL Server using ADO

 

 
Powered by phpBB® Forum Software