Removing duplicates only in count

[tchmenot]

Hello,


I have huge database around 2.5 lakh entries. I need to display some data in a specific format, but I am not able to do so.
The data looks something like this:

Customer Number	SP
647061	AG
647061	SP01
647061	AG
208689	AG
208689	SP02
208689	SP03
51982	AG
51982	GG
286432	AG
286432	AG
63482	AG
63482	SP01
63482	SP02
63482	SP03

Now I am trying to create a pivot(in excel) with the data which is easy as shown below.

Row Labels	Count of Customer Number
AG	7
GG	1
SP01	2
SP02	2
SP03	2
Grand Total	14

But here I do not want duplicates within individual rows. For e.g. AG shows 7, but the same customer 647061 has occurrence = 2. I want this to be counted as 1. But I would like it to be counted again for SP01. I tried a lot, but could not reach any conclusions. Please help.

regards.

[orange]

So do you want a list of Customers or SP?

If 1 customer can have many SP, then if you include Customer and Sp I think you'll get duplicates.
The other choice is to take Customer and only 1 SP (regardless if the Customer has 1 or 3 SPs).

[tchmenot]

Hello,

I want count of customers for an SP, but like I said removing duplicates from the no of customers.

I tried a solution which works in excel, but I do not know how to replicate in access.

regards.

So do you want a list of Customers or SP?

If 1 customer can have many SP, then if you include Customer and Sp I think you'll get duplicates.
The other choice is to take Customer and only 1 SP (regardless if the Customer has 1 or 3 SPs).

[tchmenot]

Hello,

In piece of code below, I am getting syntax error

syntax error (missing operator) in query expression 'WHERE [Product Version]='ABC'

SELECT SP,
WHERE [Product Version]='ABC',
COUNT(*) AS count_distinct_Cust
FROM (SELECT DISTINCT
SP,
[Customer Number]
FROM DataPV
) AS tmp
GROUP BY SP;

please help.

[orange]

First comma is a syntax error
WHERE comes after FROM

see this for syntax of SELECT

[orange]

Try

SELECT Count([%$##@_Alias].CUSTOMERNUMBER) AS CountOfCUSTOMERNUMBER, [%$##@_Alias].SP
FROM (SELECT DISTINCT(customernumber & " " & SP) AS XX,CustSP.CUSTOMERNUMBER , CustSP.SP
FROM CustSP
)  AS [%$##@_Alias]
GROUP BY [%$##@_Alias].SP;

which gives

CountOfCUSTOMERNUMBER
SP

5
AG

1
GG

2
SP01

2
SP02

2
SP03

[tchmenot]

Hello,

I now wrote it as
SELECT SP, COUNT(*) AS count_distinct_Cust
FROM (SELECT DISTINCT SP, [Customer Number] FROM DataPV) AS tmp
WHERE [Product Version] = 'ABC'
GROUP BY SP;

But it is not really giving the right value. It is even calculating the data when Product version is equal to any other value as well.
Do you know what could be the reason.

[tchmenot]

Hello,
It gives only the 0 for Count of customer number.

For the same query I wrote the code which I mentioned in other thread located at
MOD EDIT: threads merged


Please suggest if that will work.

[tchmenot]

This is solved...

[orange]

How about showing us your solution. Someonelse with similar problem may be searching for an answer.

[June7]

You are selecting records from a dataset that does not have [Product Version] field. How can you use it as filter criteria if the data is not present? I am surprised this query runs at all.

Review http://allenbrowne.com/subquery-01.html#Aggregation

Maybe:

SELECT SP, Count([Customer Number]) AS count_distinct_Cust FROM (SELECT DISTINCT SP, [Customer Number] FROM DataPV WHERE [Product Version]="ABC") AS tmp GROUP BY SP;