Doing a SUM

[Canman2005]

Hi all

 

I have the following table

 

UID    COST

1      1.99

2      3.99

1      1.99

3      5.22

 

As you will notice, there are 2 rows which have the same UID number

 

How can I add up all of the COSTS using a

 

SELECT SUM(cost) FROM `log`

 

But if any rows have the same UID number, then it would only count one instance of that, so with the above table it would add up all of

 

UID    COST

2      3.99

3      5.22

 

and also just one instance of

 

UID    COST

1      1.99

 

Giving a total of

 

11.2

 

Any help would be great

 

Thanks

[.josh]

distinct

[Gighalen]

I think you need to add a DISTINCT UID thingie to your SQL string. It's been a while since I've done anything, but DISTINCT UID should only select UID that are different, so the value of 1 would only appear in the SUM function once.

 

If that makes any sense >< I'm horrible at explaining things sometimes.

[Canman2005]

I tried

 

SELECT DISTINCT `uid`, SUM(cost) FROM

 

but it didnt seem to work

 

any ideas?

[Gighalen]

As said earlier, it's been ages since I've messed with DISTINCT (and SUM for that matter)...

 

I might try this:

SELECT SUM(cost) AS DISCINCT(uid) FROM table

 

 

[xtopolis]

That doesn't make sense.

 

You have uid 1, cost 1.99 twice in your table... but you want to get only one occurrence of the uid's cost value..  But what if you had uid 1 , cost 2.34, so that they were no longer matching..  How would you decide which uid 1 cost value gets selected?

 

If they are exact duplicates, why not just remove the duplicates..

 

Currently, with that data you provided, the only way I know how to get 11.2 as an answer is to

SELECT SUM(DISTINCT cost) FROM `yourTable`

But that is only because none of the other uids have a matching cost value; it will break as soon as two different uids have the same cost value

 

I say you should just remove the dupes..

[Canman2005]

That doesn't make sense.

 

You have uid 1, cost 1.99 twice in your table... but you want to get only one occurrence of the uid's cost value..  But what if you had uid 1 , cost 2.34, so that they were no longer matching..  How would you decide which uid 1 cost value gets selected?

 

If they are exact duplicates, why not just remove the duplicates..

 

Currently, with that data you provided, the only way I know how to get 11.2 as an answer is to

SELECT SUM(DISTINCT cost) FROM `yourTable`

But that is only because none of the other uids have a matching cost value; it will break as soon as two different uids have the same cost value

 

I say you should just remove the dupes..

 

Two rows with the same UID number will never have different `cost` value, therefore I need to just add up how I explained, I tried the

 

SELECT SUM(cost) AS DISCINCT(uid) FROM table

 

but that didnt work

 

Any ideas?

[Canman2005]

Has anyone got any ideas on how to do this count? or do I need to give some more info

[.josh]

I think what he's trying to say is that there is no reason you should have two rows with the exact same info.  Get rid of the duplicate row and you'll get rid of this issue....or is UID and COST not the only columns in your table, and those two rows have different values somewhere else?

[Barand]

SELECT SUM(x.cost) as total
FROM (
        SELECT DISTINCT uid, cost FROM mytablename
        ) as X

 

[Canman2005]

thanks, but that seems to come back with the following error

 

#1054 - Unknown column 'x.cost' in 'field list'

 

any ideas?

[Barand]

Is the X/x case sensitive

 

On winXP

[pre]

mysql> SELECT * FROM `canman`;

+----------+-----+------+

| idcanman | uid | cost |

+----------+-----+------+

|        1 |  1 | 1.99 |

|        2 |  2 | 3.99 |

|        3 |  1 | 1.99 |

|        4 |  3 | 5.22 |

+----------+-----+------+

 

 

mysql> SELECT SUM(x.cost) as total

    -> FROM (

    ->        SELECT DISTINCT uid, cost FROM canman

    ->        ) as X;

+-------+

| total |

+-------+

| 11.20 |

+-------+[/pre]

[Canman2005]

oh thanks mate, that seemed to work, not sure what I was doing.

 

would it be possible to use a field called `type_price` rather than `cost` with this part of the QUERY

 

SELECT DISTINCT uid, cost FROM canman

[Barand]

So long as the name of the field in the SUM() matches

[Canman2005]

something like

 

SUM( x.cost, y.type_price )

[xtopolis]

It will won't work if you have the same uid's with different costs... but that goes with what I said before, why would you store duplicate data like that?

[Barand]

No.

 

I think it better if you restate the new problem and table structure

[fenway]

No.

 

I think it better if you restate the new problem and table structure

Indeed.