calculations with queries

i am trying to calculate the sum of all records in one particular field. how do i do this using queries? do i need to create a new field in the query dedicated to this calculation? thanks

ameen

no, just use the sum() function in the query statement along with a group by.

select sum(some_field) from some_table group by some_other_field.

when i select the sum from the query i am trying to run nothing happens for the particular field. how do i get the sum for all of the data for only one field of my query?

how about showing the query you are trying to run...

well you see the query isn't very important. i am just trying to generate a value which is the sum of all the values across all records in a particular field.

i am building a clients tracking database and i am tracking quarterly information of each clients business. i have a one to many relationship between clients and year (the join table consisting of only client id, year id and year) and a one to many relationship between year and quarterly data. (one year has many (4) quarters per year of information). I can get the data of each client with no problem, it's creating totals and summaries that becomes a problem. so i am trying to do 2 things:

1)have the totals for each quarter (each record of the quarter table) of every field in the quarterly data. so i would be summing up information from quarters 1,2,3, and 4 into many fields that will hold this data. this will apply only to the same client record.

2)have the totals for ALL of the clients in each field of the quarterly data INCLUDING the totals (for each client) created in the previous step. this would display data in each quarter for all employees not just one.

i would like this total/summary information to be displayed in reports based on queries. can all of this summing be done in queries if i have to sum across many records?

please let me know if my explanation is confusing and i'll be more specific. thank you.

ameen

you have year id and year? a table for years? what does that table look like, i.e. what other columns are in there besides year id and year? and a quarter table? what does that look like?

i'm a little unclear what you mean by "summing up information from quarters 1,2,3, and 4 into many fields" and by "have the totals for ALL of the clients in each field of the quarterly data INCLUDING the totals (for each client) created in the previous step"

i'd want to see all your tables, not necessarily your query

the design of the tables will dictate what the query looks like

off the top of my head, i wouldn't need quarter and year tables, but rather just produce totals in the query by quarter and/or year


rudy

well you see the query is important. it is easier to walk through fixing one statement then it is trying to teach proper syntax through only theory and no examples.

i understand the basis of what you are doing.

you can get the desired data through a combination of sum() and group by.
so basically, in your statements that you use to get the detailed data you want, take all of the fields except the one that you want summed out of the select but leave all of the where conditions intact.
the surround the field name with sum(), and possibly give it an alias for easy reference.
then before (if you have one) the order by clause, add a group by clause to the statement and add the field name that is for the quarters and/or users depending on how you want the data summed.

i.e.
lets take a sample table
-----------------------------------

ok, now, say we want to sum the data by year and show all years in the table
select year, sum(value) as total from sample group by year, order by year
This would output something like:


now, lets say we wanted to get a sum on a quarters basis but for all years
select quarter, sum(value) as total from sample group by quarter, order by quarter
This would output something like:


now, lets say we carry the last one just a bit further and we only want to see the 1st quarter
select sum(value) as total from sample where quarter = 1 group by quarter
This would output something like:

ok. keeping the query simple works very well. i can actually pull quarterly data now. i have little more complex query that i am trying to execute now. i want to add the 1)sum of a field (for all for quarters) to 2)just the 4th quarter value of another field. how would i add these two values?

i don't think you would do this in one query.
retrieve your two values from two seperate statements and add them together then.

could i use a join query for that? from the previous explanation that you gave i have created a query that adds together all of the clients information for each quarter. so each field in the quarterly data is compiled for all the clients in the query. somehow, i would like to show these numbers as percentages of a total field that i have created. i am having trouble including a division expression within this query. is this possible? if not then where would i do the division? could i bring one query into another? thanks

ameen

no, i don't think you could use a join query for that. the problem with grouping and summing is that the more complex you make it the more likely you are that you aren't getting the data you want.

the division part of it is easy enough to do in either a) the query or b) your program
as long as you have the value you need to divide it by in the resultset also. If you don't then you can only do this in the program, but i would recommend doing this in the program also. The reason behind this is because you need to keep in mind to check for 0 on the other value because dividing by zero is not a good thing and will cause errors.
You should be able to do this easily enough in your program though.

i have decided to base the division on the query. i created a new query based on the first query (the sum of each quarter query). now i just have to divide each number by a number that is already in the query. do i declare the new percent field as an expression in the 'group by' segment? right now i am just declaring the new field:[value1 / value2] and no numbers are showing up in the field. if i don't use a query for the calculation, what would i use? i really don't want to use a form for this for reporting purposes. what to do? thanks.

ameen