SELECT COUNT(poi.poi_id),
            poi.poi_id,
       poi.name,
         oc_poi_category_id,
         TRIM(nvl(poi.street || ' ' || poi.street_nr || ' ' || poi.post_code || ' ' || poi.location || ' ' || poi.country, ' ')) Address, poi.lm_date "Date",
CASE WHEN (poi.street IS NULL OR poi.street_nr IS NULL OR poi.post_code IS NULL OR poi.location IS NULL OR poi.country IS NULL) THEN 'No'
ELSE 'Yes'
END AS "Has full address"
FROM oc_poi poi,
     wbs wb,
      operation_center op_cen,
      oc_poi_load_data DATA
WHERE poi.poi_id = wb.poi_id
AND wb.operation_center_id = op_cen.operation_center_id
AND wb.ref_number != DATA.ref_number
AND op_cen.name != DATA.operation_center_name
AND wbs_level = 1
AND UPPER(existing_record) LIKE UPPER('N')
AND poi.lm_date >= to_date('01-JAN-12','DD-MON-YY')
AND poi.LM_DATE <= to_date('01-JAN-14','DD-MON-YY')
AND to_char(poi.lm_date,'D') = '1'
AND to_char(poi.lm_date,'HH24:MI') <= '12:00'
--group by poi.poi_id, poi.name, oc_poi_category_id, poi.Address, poi."Date", "Has full address", existing_record;
GROUP BY poi.poi_id,
       poi.name,
         oc_poi_category_id,
         TRIM(nvl(poi.street || ' ' || poi.street_nr || ' ' || poi.post_code || ' ' || poi.location || ' ' || poi.country, ' ')), poi.lm_date, CASE WHEN (poi.street IS NULL OR poi.street_nr IS NULL OR poi.post_code IS NULL OR poi.location IS NULL OR poi.country IS NULL) THEN 'No'
ELSE 'Yes' END;