Distinct Count with Group By in SQL

2464 views sql
5

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.

answered question

What do you mean it does not run?

I am using Access and it gives me "Syntax error (missing operator) in query expression 'COUNT (DISTINCT pos_table.Transaction)'"

MS Access doesn't support count(distinct ...).. please refer stackoverflow.com/questions/11880199/…

1 Answer

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 t1
    WHERE Month(t1.Timestamp) = 3 AND Day(t1.Timestamp) = 3 OR Day(t1.Timestamp) = 4
)t1
GROUP BY t1.Retailer

posted this

Have an answer?

JD

Please login first before posting an answer.

Ads

Categories