CODEWITHSUNDEEP
sql-servervb.net
Ali Mkahal
Ali Mkahal

Reputation: 11

doubled and tripled sql server results

I want to create a specific table but some numeric values are doubled or tripled in the result. here is the situation:

2 tables: Payments and Expenses

[Payments]: ID, studentID, Amount, DOP (a row in this table is a payment which a student pays it on DOP (date).
[Expenses]: ID, AmountPaid, TimeStamp (a row in this table is an expense bought such as papers or pens... on a specific date(timestamp)

my query is:

select
sum(purchases.amount) as 'Income From Students',
sum(Expenses.amountpaid) as 'Expenses',
sum(purchases.amount-expenses.amountpaid) as 'Net Profit',
datename(month,timestamp) as 'Month',
datepart(year,timestamp) as 'Year' 
from expenses,purchases 
group by datename(month,timestamp),datepart(year,timestamp)

as the query tells: my table should display for each month and each year the sum of payments, expenses and net profit=payments - expenses.

the problem is, when getting the result, sum(expenses.amountpaid) is always doubled.

so any ideas...

Upvotes: 1

Views: 305

Answers (3)

Ali Mkahal
Ali Mkahal

Reputation: 11

I Solved it Guys, After 4 hours of Trying, the query is:

select sum(P.Income) as 'Income from Payments',
sum(E.expense) as 'Expenses',
sum(P.Income)-sum(E.expense) as 'Net Profit',
DateName( month , DateAdd( month , IncomeMonth , 0 ) - 1 ) as 'Month'
from
(select sum(payments.amountpaid) as Income,
month(DOP) as IncomeMonth
from payments group by month(dop)) as P,
(select sum(expenses.amountpaid) as Expense,
month(timestamp) as ExpenseMonth
from expenses 
group by month(timestamp))
as E
where
E.Expensemonth=P.IncomeMonth
group by P.IncomeMonth

Upvotes: 0

Ravi
Ravi

Reputation: 31417

SELECT T.INCOME,T.EXPENSE,SUM(T.INCOME)-SUM(T.EXPENSE) AS PROFIT 
FROM (SELECT SUM(P.amount) AS Income, SUM(E.amountpaid) AS Expense 
FROM Payments P,Expenses E WHERE P.ID=E.ID  
GROUP BY datename(month, timestamp), datepart(year, timestamp)) AS T;

Upvotes: 0

user985189
user985189

Reputation:

Sounds like you need to specify the relationship between the two tables.

Something like this, I assume:

select
sum(purchases.amount) as 'Income From Students',
sum(Expenses.amountpaid) as 'Expenses',
sum(purchases.amount-expenses.amountpaid) as 'Net Profit',
datename(month,timestamp) as 'Month',
datepart(year,timestamp) as 'Year' 
from expenses,purchases 
WHERE PURCHASES.DOP = EXPENSES.TIMESTAMP /*Add this*/
group by datename(month,timestamp),datepart(year,timestamp)

Upvotes: 3

Related Questions