Totals in a Query

[eriel3]

I want to get the an overall total, then save it to a new table.

I have a table with four fields fields

AOG, PIK, SUPP, DATE

I have a query with a date criteria, and the results pull up the dates that i requested. Question? How can I get an overall total for the three fields listed, and then take that information and place it in a new table that I created.

 

[psemianonymous]

You can just add them together like [AOG] + [PIK] + [SUPP], and to get the grand total, use something like

Sum([AOG] + [PIK] + [SUPP])


Then you can change this to something like an append query to move the result to a table.


What are you asking again?

 

[lespaul]

Why would you want to store a calculated total in a table anyway? Breaks 3NF!

You can always calculate a total in a query on the fly.

Leslie

 

[eriel3]

I am not trying to add them together, I want the total for each individual field. Okay, this is what I did first.

I created the query by selecting each field. Then I clicked on Total. Then I changed Group By to Sum. It gives me the sum of that field per record.

I want the total sum of all of the records for each individual field.

For Example:

Record 1: AOG= 1 PIK=2 SUPP=3
Record 2: AOG= 2 PIK=2 SUPP=2
Record 3: AOG= 1 PIK=1 SUPP=1
TOTAL: TOTAL AOG = 4 TOTAL PIK = 5 TOTAL SUPP=6

So how do I do that?

Oh and thanks for the quick response.

 

[lespaul]

SELECT PKFIELD, (AOG + PIK + SUPP) AS RECORDTOTAL FROM TABLENAME GROUP BY PKFIELD


but again, I MUST STRESS, that storing a total in table breaks third normal form. Is this a temporary table or are you actually going to store this sum for something?

Leslie

 

[eriel3]

I am going to store this information. Could you just have the data append to another table?

 

[psemianonymous]

Build a query, based on his SQL shown above, and you can use the query just like a table, in 99% of all cases. For the other 1% of the time where you must have a table, use some sort of "freshly populated" temp table that fills itself from this query. But you may never even need that 1%, so you can just use the query form. Trust us, this will help you out in the short run AND the long run.


Pete

 

[lespaul]

So, what happens when a user realizes that they need to change AOG in a record? How are you going to trigger an update to change the total? Are you going to manually fix the total table? What if they need to change the AOG in 10,000 records?

That's why you shouldn't store totals. Why don't you explain to us what you are using this total for and we'll see if there is some other way to acheive this without breaking 3NF.



Leslie

 

[eriel3]

Okay, sorry for not responding so soon, had to go out of town, anywho?

The totals are not what i am storing, I am using the totals so that i can calculate a pricing field.

This is what I am doing

I need the total of AOG (fyi this is in gallons)
Then I have another table with AOGPRICE
What I want to find out the price per gallon

In order for me to do this I will need to:
AOG/AOGPRICE

And then I want to do the same for PIK and SUPP

i do not need to add aog+pik+supp together

 

[psemianonymous]

I don't know what you're asking that I haven't already answered.

The one-line answer to your original question is "use Sum() around each field to get the total, for each field." The details of this you can find via help files or the like.


Feel free to read up on Sum(), the GROUP BY clause, and queries in general, and come back if you have something else to ask.

 

[eriel3]

okay this is what i am trying to do. i looked on the internet to see samples of grand totals. and i so this statement

select O.OrderID,convert(char(10),O.OrderDate,101) 'Order Date',O.OrderAmt,
case when OrderID = (select top 1 OrderId from Orders
where convert(char(10),OrderDate,101)
= convert(char(10),O.OrderDate,101)
order by OrderID desc)
then (select cast(sum(OrderAmt) as char(10))
from Orders
where OrderID <= O.OrderID
and convert(char(10),OrderDate,101)
= convert(char(10),O.OrderDate,101))
else ' ' end as 'Sub Total',
case when OrderID = (select top 1 OrderId from Orders
order by OrderDate desc)
then (select cast(sum(OrderAmt) as char(10))
from Orders)
else ' ' end as 'Grand Total'
from Orders O
order by OrderID

Output from the SELECT statement looks like this:

OrderID Order Date OrderAmt Sub Total Grand Total
----------- ---------- ---------- ---------- -----------
1 10/11/2003 10.50
2 10/11/2003 11.50
3 10/11/2003 1.25 23.25
4 10/12/2003 100.57
5 10/12/2003 19.99 120.56
6 10/13/2003 47.14
7 10/13/2003 10.08
8 10/13/2003 7.50
9 10/13/2003 9.50 74.22 218.03

This is what I am trying to do, i have a column that I want to get the grand total

The column is AOG, so should i created a query and add another column called AOG_TOTAL? and If i am do that what do I do next. I understand little sql.

 

[lespaul]

This is getting more confusing than ever!

You said you want to sum up the AOG, but now you say you want a grand total?

For Example:

Record 1: AOG= 1 PIK=2 SUPP=3
Record 2: AOG= 2 PIK=2 SUPP=2
Record 3: AOG= 1 PIK=1 SUPP=1
TOTAL: TOTAL AOG = 4 TOTAL PIK = 5 TOTAL SUPP=6

to get the above:

SELECT SUM(AOG), SUM(PIK), SUM(SUPP) FROM TableName WHERE SomeDate = #05/01/2004#

If you have some key field:

SELECT KEYFIELD, SUM(AOG), SUM(PIK), SUM(SUPP) FROM TableName WHERE SomeDate = #05/01/2004# GROUP BY KEYFIELD





Leslie

 

[eriel3]

Disregard everything else, in a nut shell this is what I am trying to do

Date AOG
04/01/04 300
04/02/04 400
04/03/04 500
04/04/04 600
TOTAL 1800

 

[lespaul]

SELECT SUM(AOG) FROM TABLENAME

Leslie