#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    1
    Rep Power
    0

    Having a problem with HAVING


    The "HAVING" statement from the query below removes all rows where the "name" field starts with "Port" and it removes all rows where count is less than 11. What I want is to removes all rows where the "name" field starts with "Port" and the count for that row is less than 11.

    Any help or suggestions will be much appreciated.

    Thanks -

    SELECT deviceEventClassId "Event ID", deviceAddress "Device Address", name "Event Name", deviceHostName "Device Host Name", count( concat(deviceEventClassId, deviceHostName, name)) "Count"
    FROM events
    WHERE (deviceVendor = "Bilbo" and
    name not like '%config%)
    group by deviceHostName, deviceEventClassId, name
    HAVING (name not like ('Port%') and
    count(concat(deviceEventClassId, deviceHostName, name)) > 11)
    order by deviceHostName
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    spaceBAR Central
    Posts
    229
    Rep Power
    42
    Try these two statements and see if this is what you are looking for:
    Code:
    with dcnt as(
      select deviceeventclassid, devicehostname, name, count(*) as dev_count
       from  events
      where  devicevendor  =  'Bilbo'
       and   name not like '%config%'
      group by deviceeventclassid, devicehostname, name )
    select d.deviceeventclassid as "Event ID", e.deviceaddress as "Device Address", d.name as "Event Name",
           d.devicehostname as "Device Host Name", d.dev_count as "Count"
     from  dcnt d join events e on d.deviceeventclassid  =  e.deviceeventclassid
                               and d.devicehostname      =  e.devicehostname
                               and d.name                =  e.name
    where  d.name not like 'Port%'
      or   ( d.name like 'Port%'
     and     d.dev_count  <  11 )
    order by d.devicehostname
    
    select deviceEventClassId "Event ID", deviceAddress "Device Address", name "Event Name", deviceHostName "Device Host Name",
           count( concat(deviceEventClassId, deviceHostName, name)) "Count"
     from  events
    where  deviceVendor  =  'Bilbo'
     and   name not like '%config%'
    group by deviceHostName, deviceEventClassId, name
    having name not like 'Port%'
      or   ( name like 'Port%'
     and     count( concat( deviceEventClassId, deviceHostName, name ) ) > 11 )
    order by deviceHostName

IMN logo majestic logo threadwatch logo seochat tools logo