Count unique customers

Question

Thank you in advance for your help. I have been working on this problem for hours. I have a table with the following columns:

OrderID | CustomerID | Date      | Course
--------|------------|-----------|----------
14954   | 13440      |16.10.2016 | Zürich
14955   | 13441      |17.10.2016 | Bern
14956   | 13441      |17.10.2016 | Aargau
14957   | 13442      |17.10.2016 | Bern
14958   | 10483      |17.10.2016 | Zürich
14959   | 13442      |18.10.2016 | Solothurn

I'd like to count the customer's first order, which was received on a certain date broken down by course. The query should yield the following result for the 17.10.2016.

Bern: 2
Aargau: 0
Zürich: 1

I've already tried a DISTINCT and nested Query like:

SELECT Count(*) AS Anzahl 
FROM   (SELECT DISTINCT kundennummer 
        FROM   (SELECT datum, 
                       kundennummer 
                FROM   unterlagenbestellungen 
                WHERE  kurs LIKE 
       '%" & DgvStatUnterlagenbestellungen.Rows(x).HeaderCell.Value & "%') 
        WHERE  datum BETWEEN # " & mindatum & " # AND # " & maxdatum & " # )

I'm very thankful for any help

score 0 · Answer 1 · answered Oct 18 '16 at 08:21

0

Try This :

SELECT 
 cource,
 COUNT(customer_id) 
FROM
 [your table]
WHERE DATE = '2016-06-17' 
GROUP BY cource 
ORDER BY cource

answered Oct 18 '16 at 08:21

Shanka SMS

644
6
15

score 0 · Accepted Answer · answered Oct 18 '16 at 08:29

This should work in Access, too:

SELECT t1.Course, Count(t2.OrderID)
FROM tab AS t1
LEFT JOIN -- get all Courses for that day
 ( -- find the minimum order per customer
   SELECT CustomerID, Min(OrderID) AS OrderID
   FROM tab AS t2
   WHERE Date='17.10.2016'
   GROUP BY CustomerID
 ) AS t2
ON t1.CustomerID = t2.CustomerID 
AND t1.OrderID = t2.OrderID -- only one row per customer
WHERE Date='17.10.2016'
GROUP BY t1.Course

Count unique customers

2 Answers2