# SQL Sum

This topic is 5121 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

## Recommended Posts

I want to sum a variable number of fields in one record. How do i do this with sql. For example field 2 to 7 or 3 to 5 thanx in advance

##### Share on other sites
Basic SQL SUM syntax goes like this:

SELECT SUM(SomeFieldName) AS MySum FROM SomeTable WHERE [Condition]

This will return a table with one field and one row, containing the sum of SomeFieldName in all rows from MySum that match the condition. You can do multiple sums like this:

SELECT SUM(FirstField) AS Sum1, SUM(SecondField) AS Sum2, SUM(SomeOtherField) AS Sum3 FROM SomeTable WHERE [Condition]

If you're wanting to do something more specific than that, it'd help if you could describe it in a little more detail (how your tables are set up, which sums you are trying to calculate, etc.)

##### Share on other sites
I have a table with 40 fields. Each field is a year. Then a user chooses a year en the i will subtract from a certain value the sum of the fields until that year that was entered. For example field 1 is 2003 and i want until 2010. So i enter 2010 as parameter. Then i will add field 1 to 7(=2010) together and subtract it from the main value

##### Share on other sites
To use SUM, you need to have a GROUP BY query.

##### Share on other sites
Can i do it without sum? So yes, how??

##### Share on other sites
You seriously need to redesign your database's architecture.

Having a table with a separate field for each year is bad. Instead you need to move all that "yearly data" into a separate table, with the id field of the original table as a foreign key, a field that specifies the year, and a field with the value for that year. Then you will be able to do all those queries easily.

With your current design you wont be able to use sum() because it is for adding up values from the same field in different records.

shmoove

##### Share on other sites
What schmoove says is true.

What you will have to do, in your case is to write a query to pull out the row you need and then perform your calculations IN CODE and write the resulting data back into the database record.

##### Share on other sites
Illustration:

A record with your current design:
-------------ID - 1Name - Joe2000 - 142001 - 252002 - 432003 - 35-------------

Would become:

In the main table:
-------------ID - 1Name - Joe-------------

In the "yearly data" table:
-------------ID - 1MainID - 1 (this is the foreign key that connects you to the main table)Year - 2000Value - 14-------------ID - 2MainID - 1 (this is the foreign key that connects you to the main table)Year - 2001Value - 25-------------ID - 3MainID - 1 (this is the foreign key that connects you to the main table)Year - 2002Value - 43-------------ID - 4MainID - 1 (this is the foreign key that connects you to the main table)Year - 2003Value - 35-------------

Then you will be able to do what you want with a query like
SELECT SUM(Value) from YearlyData AS yd, Main AS m WHERE m.Name = "Joe" AND yd.Year > 2001 AND yd.Year < 2003 AND m.ID = yd.MainID

shmoove

##### Share on other sites
Thanx i will try to do it this way

1. 1
Rutin
19
2. 2
3. 3
JoeJ
16
4. 4
5. 5

• 26
• 20
• 13
• 13
• 17
• ### Forum Statistics

• Total Topics
631700
• Total Posts
3001781
×