I have two tables - Sales(ID, Staff_Name, Sale_Date, Amount), Expense(ID, Expense_Name, Expense_Date, Amount)
The problem is to display the sum of amount for sales and expense in two columns for a given date.
The following code works in Oracle SQL:
select sum(distinct sales.amount),sum(distinct expense.amount) from sales,expense where sales.sale_date='06-dec-1996' and expense.expense_date='06-dec-1996';
SELECT Sum(Sales.amount) FROM (SELECT DISTINCT Sales.amount FROM Sales);
select (select sum(sales.amount) from sales where sales.sale_date = '06-dec-1996' ), (select sum(expense.amount) from expense where expense.expense_date = '06-dec-1996' ) from (select count(*) from sales) as s;
fromclause is only because MS Access requires something there and doesn't support anything like
sales.amountvalue or the same
expense.amountvalue? Your totals would be incorrect.
FROMclause; always use explicit