Sign in to follow this  
Pillejunior

SQL Sum

Recommended Posts

ApochPiQ    23061
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
Pillejunior    122
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
shmoove    821
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
evolutional    1393
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 this post


Link to post
Share on other sites
shmoove    821
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