Thread: Query Question

Here is a simple query question that has me stumped.

I have a database that contains the information on vehicle maintenance
costs.  When a repair is done to a vehicle, the tag number and the amount
of the repair is recorded.

table structure runs as follows:

id        serial
po        varchar(12)
veh_no        varchar(8)
cost        real
comments    varchar(30)

I would like to be able to do a query in which I can list all of the
vehicles and a totalization of the costs, e.g. list one vehicle number, and
the sum of costs for that vehicle.  Because there are numerous entries for
each vehicle I know that some part of the query will have to include
"distinct veh_no" so that only one output for each vehicle is listed.

I have figured out a way to do this with Perl, but it takes two selects,
and I would prefer to do this with just one.

Any hints or ideas?

Thanks in advance for any assistance.

Regards - Bob Kruger 

Bob Kruger <bkruger@mindspring.com> writes:
> I would like to be able to do a query in which I can list all of the
> vehicles and a totalization of the costs, e.g. list one vehicle number, and
> the sum of costs for that vehicle.

I think you want GROUP BY, eg
select veh_no, sum(cost) from table group by veh_no;

sum() and other aggregates apply across a group, not across the whole
table, when you use GROUP BY.  Notice you do NOT use DISTINCT ...
GROUP BY takes care of that for you.
        regards, tom lane

At 18:01 +0300 on 16/06/1999, Bob Kruger wrote:


>
> Here is a simple query question that has me stumped.
>
> I have a database that contains the information on vehicle maintenance
> costs.  When a repair is done to a vehicle, the tag number and the amount
> of the repair is recorded.
>
> table structure runs as follows:
>
> id        serial
> po        varchar(12)
> veh_no        varchar(8)
> cost        real
> comments    varchar(30)
>
> I would like to be able to do a query in which I can list all of the
> vehicles and a totalization of the costs, e.g. list one vehicle number, and
> the sum of costs for that vehicle.  Because there are numerous entries for
> each vehicle I know that some part of the query will have to include
> "distinct veh_no" so that only one output for each vehicle is listed.

Do you mean

SELECT veh_no, sum( cost )
FROM the_table
GROUP BY veh_no;

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma