MYSQL SUM problem

Anon

I am selecting data from a table and having to group it. However, after it's grouped, I need to sum up a particular column in the result set of the group.

I'm getting the data of col1 if I do
SELECT col1 FROM table GROUP BY id;

but if I try
SELECT sum(col1) FROM table GROUP BY id;

it's not SUMing up the result set and instead it's suming up each group.

How would I generate the mySQL query to sum up after it's been grouped, not suming up each group?

calevans

If you are looking for the total for the entire table then try:

SELECT sum(col1) FROM table;

Cal

Cal Evans
Senior Internet Dreamer
http://www.calevans.com
*

Anon

Well, I need to group the results first, then sum up the column, not capture the sum of the table.

There are multiple IDs in the table that aren't unique.

ID|Cost
1|1.00
2|2.00
1|1.00

I want to group the IDs which will result in

ID|Cost
1|1.00
2|2.00

Now, what I want to do is sum up the Cost column which should result in 3.00.

The problem I'm getting is that it's suming up all groups:

ID|Cost
1|2.00
2|2.00

That is not what I want, so I'm hoping there is a SQL way around this. Otherwise, I'm running through each row with PHP and adding the total one by one which is quite slow when there are expected to have thousands of IDs. . .

[deleted]

"Well, I need to group the results first, then sum up the column, not capture the sum of the table."

Those two are identical.

What you want in your example is take only the first Cost per ID, and add those.
That has nothing to do with SUMming or GROUPing.

Your best bet would be to create a temp table that has a unique constraint on the ID column. Then copy all the records into the temp table;
INSERT INTO temp_table SELECT * FROM real_table

That will allow only one record per ID value.
Then use a SUM to get the total.

Note: this method takes an artbitrary value for Cost. Technically you can get different outcomes every time you run the query, because you don't specify which of the Cost values you want, you just say 'a' value.