Apologies if this gets posted twice. My ISP lost their marbles for a few
minutes just when I sent it the first time and I havent' seen it show up.
<g>

> Hi,
>
> > > 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)

----------------------
"To invent, you need a good imagination and a pile of junk."
Thomas Edison