Need to Calculate the sum for different IDs

[memkam]

Hello all,

What I have is a table with two fields: IDs and expenses.

multiple occurences of a single ID are possible and will happen. What I need to do is get the total expenses incurred per ID. Not too sure if I need to use the sum functionality or if there is another way to do it.

Thanks in advance.

Maria

 

[lespaul]

Does something like this work:

SELECT IDS, SUM(*) FROM TBLNAME GROUP BY IDS

Leslie

 

[memkam]

This is what my code looks like.. but no luck

SELECT A.Name, A.Number, B.Name, B.ID, B.Number, A.Expenses FROM A, B
WHERE A.Number = B.Number
Group BY B.ID;

Thanks again!

 

[lespaul]

you didn't mention two tables (FROM A, B) in your first post:

What I have is a table with two fields: IDs and expenses.

What is your table name? It's not really "A" is it?

you also don't have a SUM in the query you posted? how do you expect to get a total without SUM?

If you don't provide complete information, it's hard to provide a complete answer. So:

1. What is/are the tables that the information is coming from?
2. If there is more than one table, how are the tables related?
3. What are the fields in the tables?
4. What does the data look like in the table?
5. What do you want your results to look like after you run the query? (using the sample data from #4)



Leslie

 

[DanJR]

Columns in a SELECT clause will usually need to be specified in the GROUP BY clause unless the column is an argument within an aggregate function.

SELECT [COLOR=red]A.Name[/color], [COLOR=red]A.Number[/color], [COLOR=red]B.Name[/color], B.ID, [COLOR=red]B.Number[/color], [COLOR=blue]sum([/color]A.Expenses[COLOR=blue]) AS TotalExpenses[/color]
  FROM A, B
  WHERE A.Number = B.Number
  Group BY B.ID;

Delete the stuff in red, add the stuff in blue...ie, as illustrated by lespaul's post.

Cheers.
Dan