Sign in to follow this  
Pillejunior

SQL Sum

Recommended Posts

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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
Share on other sites
Illustration:

A record with your current design:

-------------
ID - 1
Name - Joe
2000 - 14
2001 - 25
2002 - 43
2003 - 35
-------------

Would become:

In the main table:

-------------
ID - 1
Name - Joe
-------------


In the "yearly data" table:

-------------
ID - 1
MainID - 1 (this is the foreign key that connects you to the main table)
Year - 2000
Value - 14
-------------
ID - 2
MainID - 1 (this is the foreign key that connects you to the main table)
Year - 2001
Value - 25
-------------
ID - 3
MainID - 1 (this is the foreign key that connects you to the main table)
Year - 2002
Value - 43
-------------
ID - 4
MainID - 1 (this is the foreign key that connects you to the main table)
Year - 2003
Value - 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 this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this