Count Problem

[ck14nobel]

Hi,

this is my first post after learning a lot from this forum. Thanks a lot.

I hope you can help me with this problem, because I didn't find a solution yet.

I have a table of order positions with OrderIDs and CustomerIDs. Each Order can have multiple rows (order positions) in the table.

In a pivot table I have to answer the following questions:

- How many orders do I have?  --> Count (DISTINCT OrderID)

- How many customers do I have?  --> Count (DISTINCT CustomerID)

- How many customers ordered in exactly one order???

- How many customers ordered in multiple orders???

This is a short extract of my table:    

OrderID	CustomerID	Quantity	Price
1734872	1002392	4	63,7
1770372	1010522	1	50,38
1770372	1010522	2	38,57
1771172	1008542	4	43,53
1772382	1010522	4	33,45
1781662	1012682	1	13,4
1781672	1012682	4	53,61
1781682	1012682	1	13,4
1782712	1000752	2	16,72
1782712	1000752	2	16,72

As you can see customer 1010522 has two orders (1770372 and 1772382), customer 1012682 has three orders.

So I have two customers with multiple orders and three customers with exactly one order. That's what I need expressions for.

A lot of thanks in advance.

[sunny_talwar · Accepted solution]

May be this:

- How many customers ordered in exactly one order??? --> Count (DISTINCT {<CustomerID = {"=Count (DISTINCT OrderID) = 1"}>} CustomerID)

- How many customers ordered in multiple orders??? --> Count (DISTINCT {<CustomerID = {"=Count (DISTINCT OrderID) > 1"}>} CustomerID)

[ck14nobel]

Many thanks Sunny, that works for me.