Hi Woody,

> > > > Anyway, when one uses an aggregate, all columns should be

aggregated.

> > > >
> > > > So, when using a SUM, MAX or GROUP BY etc you need to use
> > > > some kind of aggregate function on all resulting columns.
> > >
> > > Martijn,
> > >
> > > What do you mean by this? Are you saying that you shouldn't have any
> > > non-aggregate columns returned in an aggregate query? That's certainly
> > what
> > > it sounds like to me, or I am I just mis-interpreting what you mean?
> >
> > As soon as you use one aggregate function on a resulting column, you
> > need to use some sort of aggregate on the other columns too.
> >
>
> What purpose would it serve? Wouldn't that negate the possibility of using
> the group by for totaling certain groups of data together? For instance,
> assuming I have a table of data containing types of steel material and

there

> are different sizes weights and costs, how would you go about producing a
> query that groups the material by type and size with totals for weight and
> cost?
>
> Here is a sample breakdown of a simple table of materials:
>
> Table metadata: (Materials)
> MaterialType VarChar(15) // examples (410, 410S, 4130)
> MaterialSize1 Numeric(10,2) // size is of the form nn X nn so it
> MaterialSize2 Numeric(10,2) // is stored by each dimension
> MaterialWgt Integer // weight is strictly integer (540
> lbs)
> MaterialCost Numeric(10,2) // cost is per lb cost
>
> Assuming you wanted to produce a report that listed each type of material
> and break it down by each size, you want to show the total weight within
> each group and the total cost. Without combining aggregrate and
> non-aggregate columns, how would you do this?
>
> My query would look like this:
>
> Select MaterialType, MaterialSize1, MaterialSize2, sum(MaterialWgt),
> sum(MaterialWgt * MaterialCost) from Materials
> Group by MaterialType, MaterialSize1, MaterialSize2
>
> In what way would you change the query not to use non-aggregate columns?
> Woody (TMW)

What are you getting at? All of your resulting columns are aggregated ...

It's not me who made the SQL rules, you know :)



With regards,

Martijn Tonies
InterBase Workbench - the developer tool for InterBase and Firebird
http://www.interbaseworkbench.com

Upscene Productions
http://www.upscene.com

"This is an object-oriented system.
If we change anything, the users object."