Re: Using DISTINCT AND SUM together SQL question

SELECT SUM(QTY) FROM
(SELECT DISTINCT ITEM, REV, QTY FROM test) as T

will do the trick (but it seems like a strange table design).

Regards,

Greg Gaughan www.thinksql.co.uk

"Mia" <mmiaa_at_cox.net> wrote in message news:3CB8572C.1C219F2B_at_cox.net...

> Thanks for your responses. I guess I wasn't clear as to what I wanted,
> which is a grand total. My data is something like this:
>
> ITEM REV QTY MISC
> 1 1 8 abc
> 1 1 8 def
> 1 2 4 ghi
> 1 2 4 jkl
> 1 2 4 mno
> 2 1 1 pqr
>
> I want to exclude redundant rows, ignoring the uniqueness of MISC. In
this
> data set, the value I'm looking for is 8+4+1=13.
>
> Any ideas how to do this? TIA,
>
> Mia
>
>
> Mia wrote:
>
> > Hi All,
> >
> > I've got a large table that I'm trying to write a query for. I don't
> > fully understand the grouping functions, and I'm having some trouble
> > getting what I want from the table. The table contains many columns but
> > I am only interested in 3 of them. Call them ITEM, REV, and QTY. I
> > (simply) want to do a SUM(QTY), but only for distinct rows of ITEM and
> > REV.
> >
> > That is to say,
> >
> > SELECT DISTINCT ITEM, REV FROM ...
> > and for that matter also
> > SELECT DISTINCT ITEM, REV, QTY FROM ...
> >
> > both give me the rows I want. But how can I get SUM(QTY) for the whole
> > set?
> >
> > TIA,
> >
> > Mia
>
Received on Sun Apr 14 2002 - 09:09:10 CEST