Author
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
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.)
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.)
Author
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
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
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
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.
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.
Illustration:
A record with your current design:
Would become:
In the main table:
In the "yearly data" table:
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
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
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
