sql

true_atlantis · 11-19-2008, 08:15 PM

How can I get this to work, this is being done on an Oracle database. Needs a group by caluse, but not sure where to put it..

select distinct(LEASE.MAC), count(*)
FROM REPORTING.LEASE
JOIN REPORTING.EQUIPMENT
ON LEASE.MAC = EQUIPMENT.MAC
WHERE count(*)>2
AND EQUIPMENT.DEVICETYPE='cm'
GROUP BY count(*);

crabboy · 11-19-2008, 10:07 PM

What exactly do you want to group by? The mac addresses or the number of duplicate entries in the lease table?

if you want to group by mac and get a count of each in the grouping, it would be something like this:

Code:

select t1.mac, count(t1.mac) from lease t1, equipment t2
where t1.mac = t2.mac and t2.devicetype = 'cm'
group by (t1.mac);