SQL grouping and aggregate functions

I have a SQL question about aggregate functions. Let's say that I have a
table of things produced at my factory. The table has 3 columns; part
number, group code, and amount produced.

 

Part_number   group_code    quantity

123                     G1                    100

124                     G1                    200

125                     G1                    100

456                     G2                    500

457                     G2                    150

 

And when I run my query, I want to return these 3 columns, plus a fourth
column that has the total for the group (this is going to be used for a
mathematical operation later).

 

Part_number    group_code    quantity   group_total

123                     G1                    100          400

124                     G1                    200          400

125                     G1                    100          400

456                     G2                    500          550

457                     G2                    150          550

 

The only way I can think of to do this is.

 

With groups as (

Select

 Group_code,

 Sum(quantity) as group_totals 

From

 Mytable

Group by

  Group_code

)

 

Select 

 a.Part_number,

 a.Group_code,

 a.Quantity,

  b.group_totals

from

 mytable a join

 groups b on

  a.group_code = b.group_code

 

which seem a little inelegant, and as if it might run slow with enough
data. Is there a better way to do this? I would like to use a SUM(),
tell it how to get the groups, but not actually group the rest of the
query, which as far as I know cannot be done. If there is some other way
to do this I am all ears.

 

 

Christopher M. Payne
CPayne@xxxxxxxxxxxxxxx
System Administrator/Programmer

 

The Crown Group
Corporate Offices
2111 Walter Reuther Drive
Warren, MI 48091-6199

 

Phone: (586) 575-9800
Direct: (586) 558-5317
Fax:  (586) 575-9856