I have this query where I can see the duplicate car_sightings because
they are grouped together.  However, what I want is the aggregate.
One record with the count of how many duplicate sightings for each
car, with the same mark, number and sighting date.      I tried one
column as
count(car_mark, car_number, car_date) as dupe_sightings and ORDER BY
dupe_sightings >1  but it wouldn't let me.
tia,
SELECT
distinct
l_e,
car_mark,
car_number,
location_city,
location_state,
sighting_date,
railroad,
destination_city,
destination_state

FROM

INTERNAL_CLM

GROUP BY

car_mark,
car_number,
sighting_date,
l_e,
location_city,
location_state,
sighting_date,
railroad,
destination_city,
destination_state

ORDER BY car_mark, car_number, sighting_date
;

-------------
I also tried a self-join, the count was right but it was on each
separate rows, I didn't get the aggregate rows totaled together
either.

SELECT
distinct
c.l_e,

c.car_mark,
c.car_number,
c.sighting_code,
c.location_city,
c.location_state,
c.sighting_date,
c.railroad,
c.destination_city,
c.destination_state

FROM INTERNAL_CLM as c
JOIN INTERNAL_CLM as d ON
c.car_mark = d.car_mark
AND c.car_number = d.car_number and
c.sighting_date = d.sighting_date and c.trip_id = d.trip_id

So I am missing something about aggregates.

thanks,