How do I used distinct to remove duplicates in this query?-CodePudding

I have the following table: Table

I am trying to write a query (that I will include in another query) to display how many account numbers there are per symbol.

I wrote the following query:

SELECT Symbol,
(SELECT DISTINCT COUNT([Account Number]) FROM [Open] T2 WHERE T2.Symbol = T1.Symbol) AS Accounts
FROM Open T1
GROUP BY [Symbol];

The query displays like this but it counts the same account number multiple times per symbol. EURUSD should have 3 and USDJPY should only have 1 next to it.

It should display like this.

I am trying to include this as part of another big table that has other information next to each symbol too.

I will appreciate any assistance.

CodePudding user response：

Access doesn't support count(DISTINCT ...). You can try

SELECT Symbol, count(*) AS Accounts
  FROM (SELECT DISTINCT Symbol, Account FROM Open) 
 GROUP BY Symbol;