SELECT 
    ST_Y(ST_Transform(geom_geolocation, 4326)) AS lat,
    ST_X(ST_Transform(geom_geolocation, 4326)) AS lng,
    COUNT(DISTINCT (street || house_no || COALESCE(localdistrict, ''))) AS anzahl_varianten,
    -- Wir setzen vor und nach jedes Feld einen Underscore
    string_agg(
        DISTINCT (
            '_' || street || '_' || ' ' || 
            '_' || house_no || '_' || ' [' || 
            '_' || COALESCE(localdistrict, 'N/A') || '_' || '] (' || stein_count_per_addr || ')'
        ), 
        ' | ' 
        ORDER BY (
            '_' || street || '_' || ' ' || 
            '_' || house_no || '_' || ' [' || 
            '_' || COALESCE(localdistrict, 'N/A') || '_' || '] (' || stein_count_per_addr || ')'
        )
    ) AS adress_details_visualisiert,
    SUM(stein_count_per_addr) AS gesamt_steine
FROM (
    -- Rohe Daten ohne TRIM
    SELECT 
        geom_geolocation, 
        street, 
        house_no, 
        localdistrict,
        COUNT(*) as stein_count_per_addr
    FROM public.sc_test
    WHERE street IS NOT NULL
    GROUP BY geom_geolocation, street, house_no, localdistrict
) AS sub
GROUP BY geom_geolocation
-- Zeige nur Punkte mit technischen Abweichungen
HAVING COUNT(DISTINCT (street || house_no || COALESCE(localdistrict, ''))) > 1
ORDER BY adress_details_visualisiert;