MySQL select sum question

safra

Hi,

I have a table with the column 'cpaid' which holds numbers. Is it possible to return the sum of column 'cpaid' for a certain select query directly from mysql?

Or can I only get the sum by doing the select query and afterwards something like:

while($myrow=mysql_fetch_row($result)){
$sum +=$myrow[3];// where 3 represents 'cpaid'
}

Thanks,
Raoul

YappyDog

You should be able to do something like:

Select sum(cpaid) as TotalCPaid from myTable
Where .............

tekky

SELECT sum(cpaid) AS total, id FROM table WHERE id=$id GROUP BY $id

I believe would be needed (this was just answered similiarly in another forum here)

jweissig

hello,

When you want to sum() a column of number you need to tell the database what number you want to sum.

For example say that you have 100 records in a table and you have two columns id and cpaid. Possibly the id column has something like 1 to 100 for all records.

You would tell the database to do something like

$sql = "SELECT id, sum(cpaid) as paid from sometable WHERE id < 50 GROUP BY cpaid";

When using sum() you have to use the "GROUP BY" statment so that the database knows where to sum() the numbers. Basically you just tell it what sumn to sum(). Then you can use the "WHERE" statment to limit the numbers in the column.

Check out the mysql manual for more information

http://www.mysql.de/doc/en/GROUP-BY-Modifiers.html.

hope this helps,
- justin

safra

Hi,

Thanks for the response.

I have tried all your suggestions also read the link about Group-by-modifiers.

Whatever I try MySQL does not return one simple row with the total sum of cpaid. It seems 'Group by' will simply group rows with the same value for this column which is useful when you are trying to sum totals in a specific year or so.

Tekky, I get error warnings when I try what you suggested.
jweissig, your suggestion returns a large number of rows.

Is it not possible to just return one total sum directly from MySQL?

Or perhaps it is the part " as... " in for example:

$sql = "SELECT id, sum(cpaid) as paid from sometable WHERE id < 50 GROUP BY cpaid";

What does this mean?

Raoul

bad76

Originally posted by safra
Tekky, I get error warnings when I try what you suggested.

Hi,
"I get error " is a bit too vague, not?

For me YappyDog's select work fine.

What's error are you getting?
You try also "echo mysql_error()" ?

For more help give us more info...

see you.

jweissig

hi,

Can you please give me more details?

What data is in your SQL table and what are the column names.

What is the exact SQL query you are running and what are the mysql results?

You tell me both these questions and i should be able to help you. the GROUP BY is meant to group numbers together so that can you sum() data in a column. Possibly you want sum() to do something differient?

You can you tell me what data you have and what you would like to do this with. i can create the query from scratch.

thanks,
- justin

YappyDog

If you want to return one row, you do not want to use a Group By clause. Try:

SELECT sum(cpaid) as paid from sometable WHERE id < 50

safra

Thanks,

YappyDog, your query does work! I thought I had to us "group by" and I first tried your code with "Group by" included, something similar as jwessig suggested.

It does work correctly without "Group by".

Thanks,

raoul