query count records

  • Thread starter Thread starter shank
  • Start date Start date
S

shank

I have the following records:

[SerialNo]
22732
22106
22106
22106
22106
21690
21690
21690

What I want is a count of unique [SerialNo].
I should be getting 3.
22732
22106
21690

However, with the following I get 8

SELECT DISTINCT Count(TempCheckPayment.SerialNo) AS CountOfSerialNo
FROM TempCheckPayment
ORDER BY Count(TempCheckPayment.SerialNo);

What query should I be using?

thanks!
 
That would get me the unique records.
But I want the "count" of 3....
I suppose I could make one more temp table, but I thought there'd be an
easier way to get the Count.

thanks

Duane Hookom said:
Try:
SELECT DISTINCT SerialNo
FROM TempCheckPayment
ORDER BY SerialNo;

--
Duane Hookom
Microsoft Access MVP


shank said:
I have the following records:

[SerialNo]
22732
22106
22106
22106
22106
21690
21690
21690

What I want is a count of unique [SerialNo].
I should be getting 3.
22732
22106
21690

However, with the following I get 8

SELECT DISTINCT Count(TempCheckPayment.SerialNo) AS CountOfSerialNo
FROM TempCheckPayment
ORDER BY Count(TempCheckPayment.SerialNo);

What query should I be using?

thanks!
Click to expand...
Click to expand...
 
I tried that route also.
My results are...

SerialNo ... NumOf
21690 ... 3
22106 ... 4
22732 ... 1

It's counting the occurances of each in the underlying table.
What I want is the number 3 to represent 3 unique Serial#s.
thanks!


Duane Hookom said:
Sorry, your 'What I want" didn't show a count column. Try:

SELECT SerialNo, Count(*) as NumOf
FROM TempCheckPayment
GROUP BY SerialNo
ORDER BY SerialNo;

--
Duane Hookom
Microsoft Access MVP


shank said:
That would get me the unique records.
But I want the "count" of 3....
I suppose I could make one more temp table, but I thought there'd be an
easier way to get the Count.

thanks

Duane Hookom said:
Try:
SELECT DISTINCT SerialNo
FROM TempCheckPayment
ORDER BY SerialNo;

--
Duane Hookom
Microsoft Access MVP


:

I have the following records:

[SerialNo]
22732
22106
22106
22106
22106
21690
21690
21690

What I want is a count of unique [SerialNo].
I should be getting 3.
22732
22106
21690

However, with the following I get 8

SELECT DISTINCT Count(TempCheckPayment.SerialNo) AS CountOfSerialNo
FROM TempCheckPayment
ORDER BY Count(TempCheckPayment.SerialNo);

What query should I be using?

thanks!
Click to expand...
Click to expand...
Click to expand...
 
shank said:
I have the following records:

[SerialNo]
22732
22106
22106
22106
22106
21690
21690
21690

What I want is a count of unique [SerialNo].
I should be getting 3.
22732
22106
21690

However, with the following I get 8

SELECT DISTINCT Count(TempCheckPayment.SerialNo) AS CountOfSerialNo
FROM TempCheckPayment
ORDER BY Count(TempCheckPayment.SerialNo);
Click to expand...


Try this:

SELECT Count(*) AS CountOfSerialNo
FROM ( SELECT DISTINCT SerialNo
FROM TempCheckPayment) As X
 
Marshall Barton said:
shank said:
I have the following records:

[SerialNo]
22732
22106
22106
22106
22106
21690
21690
21690

What I want is a count of unique [SerialNo].
I should be getting 3.
22732
22106
21690

However, with the following I get 8

SELECT DISTINCT Count(TempCheckPayment.SerialNo) AS CountOfSerialNo
FROM TempCheckPayment
ORDER BY Count(TempCheckPayment.SerialNo);
Click to expand...


Try this:

SELECT Count(*) AS CountOfSerialNo
FROM ( SELECT DISTINCT SerialNo
FROM TempCheckPayment) As X
Click to expand...

shazam!
thanks!
 
Back
Top