Distinct Count with Group By in SQL

Question

I am working in a table in SQL and attempting to get the distinct counts of transaction numbers for each retailer during certain days in my dataset. Right now this is what I have:

SELECT COUNT (*), pos_table.Retailer

FROM pos_table

WHERE Month(Timestamp) = 3 AND Day(Timestamp) = 3 OR Day(Timestamp) = 4

Group By Retailer

This gives me the counts for each retailer but when I add "COUNT (DISTINCT pos_table.Transaction) in place of COUNT (*) it does not run.

I am using Access and it gives me "Syntax error (missing operator) in query expression 'COUNT (DISTINCT pos_table.Transaction)'" — Michael Ryan, Sep 16 '18 at 20:51
MS Access doesn't support count(distinct ...).. please refer https://stackoverflow.com/questions/11880199/how-do-i-count-unique-items-in-field-in-access-query — Jaydip Rakholiya, Sep 16 '18 at 20:53

D-Shih · Accepted Answer · 2018-09-16T21:03:17.173

1

Access didn't support count(distinct [colunm]) you can try to write a subquery to make it.

select COUNT(t1.Transaction),t1.Retailer
from (
    SELECT DISTINCT t1.Transaction,t1.Retailer
    FROM pos_table AS t1
    WHERE Month(t1.Timestamp) = 3 AND Day(t1.Timestamp) = 3 OR Day(t1.Timestamp) = 4
) AS t1
GROUP BY t1.Retailer

edited Sep 16 '18 at 21:03

answered Sep 16 '18 at 20:54

D-Shih

44,943
6
31
51

When I do that it prompts me for the transaction number and retailer – Michael Ryan Sep 16 '18 at 20:57
@MichaelRyan Try to use Ailsa name on table and make sure the column name is correct. – D-Shih Sep 16 '18 at 20:59
It still prompts me for the Transaction number, not sure where since it should only be outputting that info – Michael Ryan Sep 16 '18 at 21:04
Are you sure `Transaction` in your table or could you show use your table info – D-Shih Sep 16 '18 at 21:04

Distinct Count with Group By in SQL

1 Answers1