PGSQL: SORT isnt executed with rollup having only one result

I'm having this code:

SELECT "monat", 
       CASE 
         WHEN count("erstkontakt_reaktionsdauer") < 3 
            THEN null 
         ELSE round(round(count("erstkontakt_reaktionsdauer"),2) / nullif(count("erstkontakt_reaktionsdauer"),0), 4) 
        END, 
        count("erstkontakt_reaktionsdauer") 
FROM "schema"."tablename" 
WHERE "monat" IN ('2021-04-01') 
  AND "monat" IS NOT NULL 
GROUP BY rollup(1 ) 
HAVING count(*) >= 1 
ORDER BY 1 NULLS FIRST

While this worked all along, When I have more than one month in

 WHERE "monat" IN ('2021-04-01')

When I have only one inside the WHERE, the ROLLUP NULL isnt coming first

enter image description here

enter image description here

1 answer

  • answered 2021-06-23 07:33 Kapsonfire

    Using a temporary table works

    SELECT * FROM (
         SELECT "monat", CASE WHEN count("erstkontakt_reaktionsdauer") < 3 THEN null ELSE round(round(count("erstkontakt_reaktionsdauer"),2) / nullif(count("erstkontakt_reaktionsdauer"),0), 4) END, count("erstkontakt_reaktionsdauer") FROM "schema"."table" WHERE "monat" IN ('2021-03-01') AND "monat" IS NOT NULL GROUP BY rollup(1 ) HAVING count(*) >= 1 
    ) t ORDER BY 1 NULLS FIRST
    

    The Problem is, when there is only one result of the query (Rollup isnt counted), the SORT method isnt called. Using a temporary table solves this.