sum a quantity without listing

[Kristi]

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?

[Daniel Reber]

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?

[C. E. Buttles]

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?

[Colin McGuigan]

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

[C. E. Buttles]

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
>
>
>