Counting distinct?

[Mark]

Hi All,

I’m trying to do a simple query counting the number of distinct
“nest_to_cont†grouped by ship_zone and datepart(“Hâ€, dtimecre).
Not sure why I can’t get this to work. I can create another query using the
distinct on “nest_to_cont†then build a query on it, but I know there is a
better way.
Right now I am counting all “Nest_to_Cont†no matter if it is a duplicate
number. With the tbMain data below I expect the report to show the results
of:

Ship_zone, DTIMECRE, Nest_to_cont
802 8 1
803 8 1

What I’m getting is:
Ship_zone, DTIMECRE, Nest_to_cont
802 8 3
803 8 2

How do I count this field as distinct and not count the duplicates?

Data:

tbMain
SHIP_ZONE DTIMECRE NEST_TO_CONT
802 1/23/2009 8:22:15 AM 000312820802
802 1/23/2009 8:22:32 AM 000312820802
802 1/23/2009 8:56:20 AM 000312820802
803 1/23/2009 8:10:29 AM 000312839802
803 1/23/2009 8:10:58 AM 000312839802

Query I have now that is getting the wrong results.

SELECT tbMain.SHIP_ZONE, DatePart("h",[DTIMECRE]) AS [Work Hour],
Count(tbMain.NEST_TO_CONT) AS CountOfNEST_TO_CONT
FROM tbMain
GROUP BY tbMain.SHIP_ZONE, DatePart("h",[DTIMECRE]);

Query Results (not based on data above)
Query
SHIP_ZONE Work Hour CountOfNEST_TO_CONT
802 8 218
802 9 17
803 8 121

 

[Golfinray]

In your query, instead of using just Select, use Select Distinct.

 

[Mark]

I tried that, but it still doesn't give me distinct over the Nest_to_Cont
field. It counts them all instead of counting just unique numbers. Here is
my query.

SELECT DISTINCT tbMain.SHIP_ZONE, DatePart("h",[DTIMECRE]) AS t,
Count(tbMain.NEST_TO_CONT) AS CountOfNEST_TO_CONT
FROM tbMain
GROUP BY tbMain.SHIP_ZONE, DatePart("h",[DTIMECRE]);

In your query, instead of using just Select, use Select Distinct.

Hi All,

I’m trying to do a simple query counting the number of distinct
“nest_to_cont†grouped by ship_zone and datepart(“Hâ€, dtimecre).
Not sure why I can’t get this to work. I can create another query using the
distinct on “nest_to_cont†then build a query on it, but I know there is a
better way.
Right now I am counting all “Nest_to_Cont†no matter if it is a duplicate
number. With the tbMain data below I expect the report to show the results
of:

Ship_zone, DTIMECRE, Nest_to_cont
802 8 1
803 8 1

What I’m getting is:
Ship_zone, DTIMECRE, Nest_to_cont
802 8 3
803 8 2

How do I count this field as distinct and not count the duplicates?

Data:

tbMain
SHIP_ZONE DTIMECRE NEST_TO_CONT
802 1/23/2009 8:22:15 AM 000312820802
802 1/23/2009 8:22:32 AM 000312820802
802 1/23/2009 8:56:20 AM 000312820802
803 1/23/2009 8:10:29 AM 000312839802
803 1/23/2009 8:10:58 AM 000312839802

Query I have now that is getting the wrong results.

SELECT tbMain.SHIP_ZONE, DatePart("h",[DTIMECRE]) AS [Work Hour],
Count(tbMain.NEST_TO_CONT) AS CountOfNEST_TO_CONT
FROM tbMain
GROUP BY tbMain.SHIP_ZONE, DatePart("h",[DTIMECRE]);

Query Results (not based on data above)
Query
SHIP_ZONE Work Hour CountOfNEST_TO_CONT
802 8 218
802 9 17
803 8 121