Report to Count Unique Data

[Guest]

My data comes in like this:

9:30:22 0000002751620397
9:30:22 0000002751620397
9:30:22 0000002751626082
9:30:23 0000002751626082
9:30:24 0000002751632460

Col1 is Time and Col2 is ItemID. Item ID is text.

Can a report list out for each Time, the count of unique ItemID's ?

9:30:22 2
9:30:23 1
9:30:24 1

Thank you in advance.

 

[Stefan Hoffmann]

hi Carl,

Col1 is Time and Col2 is ItemID. Item ID is text.
Can a report list out for each Time, the count of unique ItemID's ?

Create a query, add your table. Add Col1 and Col2 to the output list.
Click on the greek sum symbol. Select "Group By" for Col1 and "Sum" for
Col2.

Or use this SQL:

SELECT
Col1,
Sum(Col2)
FROM
YourTableName
GROUP BY
Col1


mfG
--> stefan <--

 

[Douglas J. Steele]

First, shouldn't that be Count(Col2), not Sum(Col2)?

However, even using Count won't give Carl exactly what he wants, as he
stated he wanted "the count of unique ItemID's" (if you look at his example,
there are 3 rows at 9:30:22, but two of them have ID 000002751620397, so he
only wants a value of 2 to be returned.

The SQL would have to be something like:

SELECT
Col1,
Count(Col2)
FROM
(SELECT DISTINCT
Col1, Col2
FROM
YourTableName
) AS A
GROUP BY
Col1

 

[John Spencer]

You need a query that returns the unique values and then you can do a count
on that. Since I don't know your field names, I can only give you a
generic query.

Q1: Save the query as Q1
SELECT Distinct TimeField, ItemID
FROM YourTable

SELECT TimeField, Count(ItemID) as TheCount
FROM Q1
GROUP BY TimeField

If your table and field names don't require square brackets, you can do that
all in one query.

SELECT TimeField, Count(ItemID) as TheCount
FROM
(
SELECT Distinct TimeField, ItemID
FROM YourTable
) as UniqueItems
GROUP BY TimeField

 

[Stefan Hoffmann]

hi Douglas,

First, shouldn't that be Count(Col2), not Sum(Col2)?

Yes, some sort of typo.

However, even using Count won't give Carl exactly what he wants, as he
stated he wanted "the count of unique ItemID's" (if you look at his example,
there are 3 rows at 9:30:22, but two of them have ID 000002751620397, so he
only wants a value of 2 to be returned.

This is right, i have overread that.


mfG
--> stefan <--