DevX Home Today's Headlines   Articles Archive   Tip Bank   Forums

# Thread: sum a quantity without listing

1. Kristi Guest

## sum a quantity without listing

I have several order numbers to list, and many have the same part number in
them. User needs a report of the part number, number of occurrences of that
part number, and a total of the quantities for each order that lists the
particular part number. To save space, however, he only wants one instance
of each part number listed. I can't seem to get the total quantity to show
up without actually listing each quantity and doing a sum on the quantity
column. How do I total this column without actually listing it?

2. Daniel Reber Guest

## Re: sum a quantity without listing

Could you post your tables and relationships. That would make it easier. Thanks.

Daniel Reber, MCP

"Kristi" <kbryant@trane.com> wrote:
>
>I have several order numbers to list, and many have the same part number

in
>them. User needs a report of the part number, number of occurrences of

that
>part number, and a total of the quantities for each order that lists the
>particular part number. To save space, however, he only wants one instance
>of each part number listed. I can't seem to get the total quantity to show
>up without actually listing each quantity and doing a sum on the quantity
>column. How do I total this column without actually listing it?

3. C. E. Buttles Guest

## Re: sum a quantity without listing

Try this. You will need to fill in some specifics since I don't know what
your column names and table names, etc. are.

SELECT <some columns> FROM <tablename> WHERE <some conditions>
GROUP BY PartNumber
ORDER BY PartNumber, Quantity
COMPUTE (SUM(Quantity))

"Kristi" <kbryant@trane.com> wrote in message
news:39084abf\$1@news.devx.com...
>
> I have several order numbers to list, and many have the same part number

in
> them. User needs a report of the part number, number of occurrences of

that
> part number, and a total of the quantities for each order that lists the
> particular part number. To save space, however, he only wants one

instance
> of each part number listed. I can't seem to get the total quantity to

show
> up without actually listing each quantity and doing a sum on the quantity
> column. How do I total this column without actually listing it?

4. Colin McGuigan Guest

## Re: sum a quantity without listing

C. E. Buttles wrote in message <39086e6f\$1@news.devx.com>...
>Try this. You will need to fill in some specifics since I don't know what
>your column names and table names, etc. are.
>
>SELECT <some columns> FROM <tablename> WHERE <some conditions>
>GROUP BY PartNumber
>ORDER BY PartNumber, Quantity
>COMPUTE (SUM(Quantity))
>
>

The above has a little extra overhead (such as ordering by quantity, etc).
This could also be written as:

SELECT PartNumber, SUM(Quantity) FROM MyTable
GROUP BY PartNumber
ORDER BY PartNumber

--
Colin McGuigan

5. C. E. Buttles Guest

## Re: sum a quantity without listing

True. I had to order by quantity due to the compute clause. This is much
cleaner.

Thank you.

C. E.

"Colin McGuigan" <colin@chicor.com> wrote in message
news:39088fab@news.devx.com...
> C. E. Buttles wrote in message <39086e6f\$1@news.devx.com>...
> >Try this. You will need to fill in some specifics since I don't know

what
> >your column names and table names, etc. are.
> >
> >SELECT <some columns> FROM <tablename> WHERE <some conditions>
> >GROUP BY PartNumber
> >ORDER BY PartNumber, Quantity
> >COMPUTE (SUM(Quantity))
> >
> >

>
> The above has a little extra overhead (such as ordering by quantity, etc).
> This could also be written as:
>
> SELECT PartNumber, SUM(Quantity) FROM MyTable
> GROUP BY PartNumber
> ORDER BY PartNumber
>
>
> --
> Colin McGuigan
>
>
>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

 FAQ Latest Articles Java .NET XML Database Enterprise