CODEWITHSUNDEEP
c#mysqlsql
Sk Saad Al Mahmud
Sk Saad Al Mahmud

Reputation: 3019

total sum of values with same Name but different ID in a table

I am using mySql and C# to create a Expense Manager. It records Expenses with Expenses_ID and YearMonth . Now how can I SUM the Expenses with same Expenses_ID of different YearMonthID -eg. Total 'Electricity Bill' (Expense_ID=1) Costs of year 2012 (2012 jan, 2012 feb, 2012 mar)
Total 'Rent'(Expense_ID=2) Costs of year 2012 (2012 jan, 2012 feb, 2012 mar) and so on.. In a same query.

+--------+------------+-------+
| ym_ID  | Expense_ID | Costs |
+--------+------------+-------+
| 2012 1 |          1 |   123 |
| 2012 1 |          2 |   103 |
| 2012 1 |          3 |   100 |
| 2012 2 |          1 |   144 |
| 2012 2 |          2 |   122 |
| 2012 2 |          3 |   155 |
| 2012 3 |          1 |   177 |
| 2012 3 |          2 |   112 |
| 2012 3 |          3 |   111 |
+--------+------------+-------+

this is my table . I want a Total cost of each expenses.
Output table: Like this: 

+------------+----------------------------+
| Expense_ID | Total_Costs                |
+------------+----------------------------+
|          1 |                        444 |
|          2 |                        337 |
|          3 |                        366 |
+------------+----------------------------+

Here I have 3 Expense_ID- but in Main table it's upto 30. So i need a query to do this sum.

I have tried these:

SELECT b.Costs=a.Costs+b.Costs 
FROM `Table1` a JOIN `Table1` b 
WHERE a.Expense_ID=b.Expense_ID
      AND a.ym_ID<>b.ym_ID`

Help me out Please!.

Upvotes: 3

Views: 544

Answers (3)

John Woo
John Woo

Reputation: 263733

You need to use an aggregate function (SUM) to calculate the total cost for each Expense_ID

SELECT Expense_ID, SUM(Costs) totalCosts
FROM tableName
//  WHERE ym_ID LIKE '2012%'                   //  if you want for 2012 only
GROUP BY Expense_ID

Upvotes: 3

Huzaifa Qamer
Huzaifa Qamer

Reputation: 314

The above queries would give you sum of each expense_ID, but for getting total costs for each expense_ID for only 2012 (assuming that you have records for other years also) the following query would be better

SELECT Expense_ID, SUM(Costs) totalCosts
FROM tableName WHERE ym_ID LIKE '2012%'
GROUP BY Expense_ID

Upvotes: 0

dealer
dealer

Reputation: 474

select expense_id, sum(cost) from COST_T group by expense_id;

See this:

http://sqlfiddle.com/#!2/941e7/1

Upvotes: 0

Related Questions