I am trying to SUM only the DISTINCT rows from a particular column
from my database.

For example:

My database looks like this

ORDERID PRODUCT PRODUCTCOST ORDERTOTAL
1 Xbox $400
$600
1 Wii $200
$600
2 Controller $30
$75
2 Game $20
$75
2 Cords $25
$75


What I want to do is find the total sale(so i want to SUM only the
DISTINCT ORDERID). In this case the answer would be $675.

So I tried without success:

SELECT DISTINCT(ORDERID), SUM(ORDERTOTAL) From tablename <-- no luck


Any ideas?

Shaq-Diesel schreef:
> I am trying to SUM only the DISTINCT rows from a particular column
> from my database.
>
> For example:
>
> My database looks like this
>
> ORDERID PRODUCT PRODUCTCOST ORDERTOTAL
> 1 Xbox $400
> $600
> 1 Wii $200
> $600
> 2 Controller $30
> $75
> 2 Game $20
> $75
> 2 Cords $25
> $75
>
>
> What I want to do is find the total sale(so i want to SUM only the
> DISTINCT ORDERID). In this case the answer would be $675.
>
> So I tried without success:
>
> SELECT DISTINCT(ORDERID), SUM(ORDERTOTAL) From tablename <-- no luck
>
>
> Any ideas?

SELECT DISTINCT(ORDERID), SUM(PRODUCTCOST) From tablename
GROUP BY ORDERID

--
Luuk

On Apr 1, 4:17 pm, Luuk <L...@invalid.lan> wrote:
> Shaq-Diesel schreef:
>
>
>
> > I am trying to SUM only the DISTINCT rows from a particular column
> > from my database.
>
> > For example:
>
> > My database looks like this
>
> > ORDERID PRODUCT PRODUCTCOST ORDERTOTAL
> > 1 Xbox $400
> > $600
> > 1 Wii $200
> > $600
> > 2 Controller $30
> > $75
> > 2 Game $20
> > $75
> > 2 Cords $25
> > $75
>
> > What I want to do is find the total sale(so i want to SUM only the
> > DISTINCT ORDERID). In this case the answer would be $675.
>
> > So I tried without success:
>
> > SELECT DISTINCT(ORDERID), SUM(ORDERTOTAL) From tablename <-- no luck
>
> > Any ideas?
>
> SELECT DISTINCT(ORDERID), SUM(PRODUCTCOST) From tablename
> GROUP BY ORDERID
>
> --
> Luuk

Luuk,

This does NOT work. Although it does yield an error, it does not yield
an answer to the question. What this returns is the sum of EACH unique
ORDERID. Not the total of ALL the distinct ORDERTOTAL. That was my
guess too...

"Shaq-Diesel" <shikhir@gmail.com> wrote in message
news:17884e6c-654b-496e-9d10-4f7056343c4d@8g2000hsu.googlegroups.com...
>I am trying to SUM only the DISTINCT rows from a particular column
> from my database.
>
> For example:
>
> My database looks like this
>
> ORDERID PRODUCT PRODUCTCOST ORDERTOTAL
> 1 Xbox $400
> $600
> 1 Wii $200
> $600
> 2 Controller $30
> $75
> 2 Game $20
> $75
> 2 Cords $25
> $75
>
>
> What I want to do is find the total sale(so i want to SUM only the
> DISTINCT ORDERID). In this case the answer would be $675.
>
> So I tried without success:
>
> SELECT DISTINCT(ORDERID), SUM(ORDERTOTAL) From tablename <-- no
> luck
>
>
> Any ideas?

$600 + $600 = ERROR, because $600 is a string.

R.

On 1 Apr, 20:13, Shaq-Diesel <shik...@gmail.com> wrote:
> I am trying to SUM only the DISTINCT rows from a particular column
> from my database.
>
> For example:
>
> My database looks like this
>
> ORDERID PRODUCT PRODUCTCOST ORDERTOTAL
> 1 Xbox $400
> $600
> 1 Wii $200
> $600
> 2 Controller $30
> $75
> 2 Game $20
> $75
> 2 Cords $25
> $75
>
> What I want to do is find the total sale(so i want to SUM only the
> DISTINCT ORDERID). In this case the answer would be $675.
>
> So I tried without success:
>
> SELECT DISTINCT(ORDERID), SUM(ORDERTOTAL) From tablename <-- no luck
>
> Any ideas?

As Richard pointed out, you cannot sum strings. If you were storing
the amounts as numbers then the answer is trivial.

On Apr 2, 6:55 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 1 Apr, 20:13, Shaq-Diesel <shik...@gmail.com> wrote:
>
>
>
> > I am trying to SUM only the DISTINCT rows from a particular column
> > from my database.
>
> > For example:
>
> > My database looks like this
>
> > ORDERID PRODUCT PRODUCTCOST ORDERTOTAL
> > 1 Xbox $400
> > $600
> > 1 Wii $200
> > $600
> > 2 Controller $30
> > $75
> > 2 Game $20
> > $75
> > 2 Cords $25
> > $75
>
> > What I want to do is find the total sale(so i want to SUM only the
> > DISTINCT ORDERID). In this case the answer would be $675.
>
> > So I tried without success:
>
> > SELECT DISTINCT(ORDERID), SUM(ORDERTOTAL) From tablename <-- no luck
>
> > Any ideas?
>
> As Richard pointed out, you cannot sum strings. If you were storing
> the amounts as numbers then the answer is trivial.

Whoops. They are actually NOT stored as string but stored as numbers.
I just put the dollar sign there to make it easier to understand but
it seems it actually added to confusion.

Any Ideas still?

On 2 Apr, 13:59, Shaq-Diesel <shik...@gmail.com> wrote:
> On Apr 2, 6:55 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
>
>
> > On 1 Apr, 20:13, Shaq-Diesel <shik...@gmail.com> wrote:
>
> > > I am trying to SUM only the DISTINCT rows from a particular column
> > > from my database.
>
> > > For example:
>
> > > My database looks like this
>
> > > ORDERID PRODUCT PRODUCTCOST ORDERTOTAL
> > > 1 Xbox $400
> > > $600
> > > 1 Wii $200
> > > $600
> > > 2 Controller $30
> > > $75
> > > 2 Game $20
> > > $75
> > > 2 Cords $25
> > > $75
>
> > > What I want to do is find the total sale(so i want to SUM only the
> > > DISTINCT ORDERID). In this case the answer would be $675.
>
> > > So I tried without success:
>
> > > SELECT DISTINCT(ORDERID), SUM(ORDERTOTAL) From tablename <-- no luck
>
> > > Any ideas?
>
> > As Richard pointed out, you cannot sum strings. If you were storing
> > the amounts as numbers then the answer is trivial.
>
> Whoops. They are actually NOT stored as string but stored as numbers.
> I just put the dollar sign there to make it easier to understand but
> it seems it actually added to confusion.
>
> Any Ideas still?

SELECT
SUM(ordertotal)
FROM (
SELECT
DISTINCT orderid, ordertotal
FROM tablename
) AS a

On 2 Apr, 13:59, Shaq-Diesel <shik...@gmail.com> wrote:
> On Apr 2, 6:55 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
>
>
> > On 1 Apr, 20:13, Shaq-Diesel <shik...@gmail.com> wrote:
>
> > > I am trying to SUM only the DISTINCT rows from a particular column
> > > from my database.
>
> > > For example:
>
> > > My database looks like this
>
> > > ORDERID PRODUCT PRODUCTCOST ORDERTOTAL
> > > 1 Xbox $400
> > > $600
> > > 1 Wii $200
> > > $600
> > > 2 Controller $30
> > > $75
> > > 2 Game $20
> > > $75
> > > 2 Cords $25
> > > $75
>
> > > What I want to do is find the total sale(so i want to SUM only the
> > > DISTINCT ORDERID). In this case the answer would be $675.
>
> > > So I tried without success:
>
> > > SELECT DISTINCT(ORDERID), SUM(ORDERTOTAL) From tablename <-- no luck
>
> > > Any ideas?
>
> > As Richard pointed out, you cannot sum strings. If you were storing
> > the amounts as numbers then the answer is trivial.
>
> Whoops. They are actually NOT stored as string but stored as numbers.
> I just put the dollar sign there to make it easier to understand but
> it seems it actually added to confusion.
>
> Any Ideas still?

Alternatively, why not just
SELECT
SUM(productcost)
FROM tablenname

You will get exactly the same answer

On Apr 2, 10:19 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 2 Apr, 13:59, Shaq-Diesel <shik...@gmail.com> wrote:
>
>
>
> > On Apr 2, 6:55 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
> > > On 1 Apr, 20:13, Shaq-Diesel <shik...@gmail.com> wrote:
>
> > > > I am trying to SUM only the DISTINCT rows from a particular column
> > > > from my database.
>
> > > > For example:
>
> > > > My database looks like this
>
> > > > ORDERID PRODUCT PRODUCTCOST ORDERTOTAL
> > > > 1 Xbox $400
> > > > $600
> > > > 1 Wii $200
> > > > $600
> > > > 2 Controller $30
> > > > $75
> > > > 2 Game $20
> > > > $75
> > > > 2 Cords $25
> > > > $75
>
> > > > What I want to do is find the total sale(so i want to SUM only the
> > > > DISTINCT ORDERID). In this case the answer would be $675.
>
> > > > So I tried without success:
>
> > > > SELECT DISTINCT(ORDERID), SUM(ORDERTOTAL) From tablename <-- no luck
>
> > > > Any ideas?
>
> > > As Richard pointed out, you cannot sum strings. If you were storing
> > > the amounts as numbers then the answer is trivial.
>
> > Whoops. They are actually NOT stored as string but stored as numbers.
> > I just put the dollar sign there to make it easier to understand but
> > it seems it actually added to confusion.
>
> > Any Ideas still?
>
> Alternatively, why not just
> SELECT
> SUM(productcost)
> FROM tablenname
>
> You will get exactly the same answer

Unfortunately no. Only if it were that simple. As per your suggestion.
it would add up ALL the NON-distinct values. That would yield an
answer of 1425 (600+600+75+75+75). What I need is a way to get an
answer of 675 (600+75). In real life, the table has many thousands of
rows so it has to be distinct based on ORDERID not ORDERTOTAL

Any other guesses?

"Shaq-Diesel" <shikhir@gmail.com> wrote in message
news:fc12e9a4-e90f-47ef-9925-054f6d90dbc2@b1g2000hsg.googlegroups.com...
> On Apr 2, 10:19 am, Captain Paralytic <paul_laut...@yahoo.com>
> wrote:
>> On 2 Apr, 13:59, Shaq-Diesel <shik...@gmail.com> wrote:
>>
>>
>>
>> > On Apr 2, 6:55 am, Captain Paralytic <paul_laut...@yahoo.com>
>> > wrote:
>>
>> > > On 1 Apr, 20:13, Shaq-Diesel <shik...@gmail.com> wrote:
>>
>> > > > I am trying to SUM only the DISTINCT rows from a particular
>> > > > column
>> > > > from my database.
>>
>> > > > For example:
>>
>> > > > My database looks like this
>>
>> > > > ORDERID PRODUCT PRODUCTCOST ORDERTOTAL
>> > > > 1 Xbox $400
>> > > > $600
>> > > > 1 Wii $200
>> > > > $600
>> > > > 2 Controller $30
>> > > > $75
>> > > > 2 Game $20
>> > > > $75
>> > > > 2 Cords $25
>> > > > $75
>>
>> > > > What I want to do is find the total sale(so i want to SUM
>> > > > only the
>> > > > DISTINCT ORDERID). In this case the answer would be $675.
>>
>> > > > So I tried without success:
>>
>> > > > SELECT DISTINCT(ORDERID), SUM(ORDERTOTAL) From tablename <--
>> > > > no luck
>>
>> > > > Any ideas?
>>
>> > > As Richard pointed out, you cannot sum strings. If you were
>> > > storing
>> > > the amounts as numbers then the answer is trivial.
>>
>> > Whoops. They are actually NOT stored as string but stored as
>> > numbers.
>> > I just put the dollar sign there to make it easier to understand
>> > but
>> > it seems it actually added to confusion.
>>
>> > Any Ideas still?
>>
>> Alternatively, why not just
>> SELECT
>> SUM(productcost)
>> FROM tablenname
>>
>> You will get exactly the same answer
>
> Unfortunately no. Only if it were that simple. As per your
> suggestion.
> it would add up ALL the NON-distinct values. That would yield an
> answer of 1425 (600+600+75+75+75). What I need is a way to get an
> answer of 675 (600+75). In real life, the table has many thousands
> of
> rows so it has to be distinct based on ORDERID not ORDERTOTAL
>
> Any other guesses?

SUM(productcost) is 400 + 200 + 30 +20 +25 = ?

R.

Shaq-Diesel schreef:
> On Apr 1, 4:17 pm, Luuk <L...@invalid.lan> wrote:
>> Shaq-Diesel schreef:
>>
>>
>>
>>> I am trying to SUM only the DISTINCT rows from a particular column
>>> from my database.
>>> For example:
>>> My database looks like this
>>> ORDERID PRODUCT PRODUCTCOST ORDERTOTAL
>>> 1 Xbox $400
>>> $600
>>> 1 Wii $200
>>> $600
>>> 2 Controller $30
>>> $75
>>> 2 Game $20
>>> $75
>>> 2 Cords $25
>>> $75
>>> What I want to do is find the total sale(so i want to SUM only the
>>> DISTINCT ORDERID). In this case the answer would be $675.
>>> So I tried without success:
>>> SELECT DISTINCT(ORDERID), SUM(ORDERTOTAL) From tablename <-- no luck
>>> Any ideas?
>> SELECT DISTINCT(ORDERID), SUM(PRODUCTCOST) From tablename
>> GROUP BY ORDERID
>>
>> --
>> Luuk
>
> Luuk,
>
> This does NOT work. Although it does yield an error, it does not yield
> an answer to the question. What this returns is the sum of EACH unique
> ORDERID. Not the total of ALL the distinct ORDERTOTAL. That was my
> guess too...
>
>
>

i must have mis-read you question, but now Captain Paralytic already
gave the right answer...

or you yourself could start with the above, and find out yourself how to
get the requested value....

i think you will get more satisfied if you find your own solution.. ?




--
Luuk