Help with Counting Distinct

[calleng]

Could someone tell me if it is possible to do what I am trying to do.

3 fields in a table - DateTime, LocalAddr, and TargetAddr

I am trying to output to a second table that would contain

LocalAddr with a count of the number of

DISTINCT LocalAddr

Example output:

LocalAddr Number
xxx.xxx.xxx.001 50
xxx.xxx.xxx.002 102
xxx.xxx.xxx.003 78

 

[hmckillop]

what is the sample of data in the original table.

Based on what I can see you could just do
SELECT DISCTINCT (LocalAddr), COUNT(*)
FROM MyTable
Group By LocalAddr


"I'm living so far beyond my income that we may almost be said to be living apart

 

[Lothario]

SELECT LocalAddr, COUNT(*) AS TOTALCOUNT FROM yourtable GROUP BY LocalAddr

-L

 

[SQLBill]

Just add the INSERT command to hmckillop's SELECT.

INSERT INTO secondtbl
SELECT DISTINCT LocalAddr, COUNT(LocalAddr)
FROM firsttbl
GROUP BY LocalAddr

-SQLBill

Posting advice: FAQ481-4875

 

[calleng]

Thanks - that is exactly what I needed.

 

[danceman]

DISTINCT is not needed in this. COUNT(localaddr) with the group by localaddr is the key. If you want to get a listing based on localaddr count add HAVING

SELECT LocalAddr, COUNT(LocalAddr)
FROM firsttbl
GROUP BY LocalAddr HAVING COUNT(LocalAddr) > 3


Attitude is Everything