user55844 user55844 - 1 year ago 74
SQL Question

Distinct Aggregate function in Access Query

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';

But since MS Access does not support distinct inside the aggregate function, i tried this (which works):

SELECT Sum(Sales.amount) FROM (SELECT DISTINCT Sales.amount FROM Sales);

But i could not get to work what i actually need. How can i do it in MS Access?

Answer Source

Use subqueries:

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;


  • The from clause is only because MS Access requires something there and doesn't support anything like dual.
  • The date formats might need to be adjusted.
  • This version is better than your version. What if two rows had the same sales.amount value or the same expense.amount value? Your totals would be incorrect.
  • Never use commas in the FROM clause; always use explicit JOIN syntax.