Postgres Nested WHEN Aggregate Function

Question

In PostgreSQL (version 9.4) I am trying to construct a query to determine in various tranches how many times various numbers appear in the data set between ranges. When I query group-ing by "SettlementPointPrice" the count() function works correctly bucketing prices into individual tranches as I would expect. However, this creates hundreds of rows. I am looking for the ability (sub-query?) to aggregate the sum of count()'s in each bucket into one single row. What is the best way to manage this in SQL?

I am using a statement like this (full SQL below) for each bucket/tranche:

CASE WHEN (round(sum("DA-A"."SettlementPointPrice"),2)) BETWEEN 0 AND 10
     THEN count(*) ELSE 0 END AS "DA $0 - $10",

When I group by "SettlementPointPrice" (two prices in this example) to confirm the data counts the buckets correctly. As the table below shows.

Raw data from individual two days:

Row | "SettlementPointPrice" | 0-10 | 11-20 | 21-30
1   | 18                     | 0    | 1     | 0
2   | 22                     | 0    | 0     | 1

However, I am unable to get the aggregated summation by grouping them all together. I assume this is a sub-query?

I would like the result to be as such:

Row | 0-10 | 11-20 | 21-30
1   | 0    | 1     | 1

Full SQL code:

SELECT
  "DA-A"."SettlementPointPrice",
  CASE WHEN (round(sum("DA-A"."SettlementPointPrice"),2)) BETWEEN 0 AND 10
       THEN count(*) ELSE 0 END AS "DA $0 - $10",
  CASE WHEN (round(sum("DA-A"."SettlementPointPrice"),2)) BETWEEN 11 AND 20
       THEN COUNT(*) ELSE 0 END AS "DA $11 - $20",
  CASE WHEN (round(sum("DA-A"."SettlementPointPrice"),2)) BETWEEN 21 AND 30
       THEN COUNT(*) ELSE 0 END AS "DA $21 - $30"
FROM 
  public.da "DA-A", 
  public.rt_aggregate "RT-A"
WHERE 
  "RT-A"."DeliveryDate" = "DA-A"."DeliveryDate" AND
  "RT-A"."SettlementPointName" = "DA-A"."SettlementPointName" AND
  "DA-A"."SettlementPointName" = 'John' AND 
  "DA-A"."DeliveryDate" >= '2015-02-01' AND
  "DA-A"."DeliveryDate" <= '2015-02-20' AND
 ("RT-A"."DeliveryHour" = 14) and 
  date_part('hour', "DA-A"."DeliveryHour") = "RT-A"."DeliveryHour"
GROUP BY
  "DA-A"."SettlementPointPrice",
  "DA-A"."SettlementPointName"

Answer 1

After some processing this boiled down to:

While your predicate d."SettlementPointName" = 'John' is filtering a single value for "SettlementPointName" anyway, simplify to:

SELECT count(                                     d."SettlementPointPrice" < 10.5 OR NULL) AS da_00_10
     , count(d."SettlementPointPrice" >= 10.5 AND d."SettlementPointPrice" < 20.5 OR NULL) AS da_11_20
     , count(d."SettlementPointPrice" >= 20.5 AND d."SettlementPointPrice" < 30.5 OR NULL) AS da_21_30
FROM   public.da d
JOIN   public.rt_aggregate r USING ("DeliveryDate", "SettlementPointName")
WHERE  d."SettlementPointName" = 'John'
AND    d."DeliveryDate" >= '2015-02-01'
AND    d."DeliveryDate" <= '2015-02-20'
AND    r."DeliveryHour" = 14
AND    date_part('hour', d."DeliveryHour") = r."DeliveryHour";

About the counting technique:

• For absolute performance, is SUM faster or COUNT?

Or better, yet, use the new aggregate filter technique in pg 9.4:

SELECT d."SettlementPointName"
     , count(*) FILTER (WHERE d."SettlementPointPrice" <  10.5) AS da_00_10
     , count(*) FILTER (WHERE d."SettlementPointPrice" >= 10.5
                        AND   d."SettlementPointPrice" <  20.5) AS da_11_20
     , count(*) FILTER (WHERE d."SettlementPointPrice" >= 20.5
                        AND   d."SettlementPointPrice" <  30.5) AS da_21_30
FROM   public.da d
JOIN   public.rt_aggregate r USING ("DeliveryDate", "SettlementPointName")
WHERE  d."DeliveryDate" >= '2015-02-01'
AND    d."DeliveryDate" <= '2015-02-20'
AND    r."DeliveryHour" = 14
AND    date_part('hour', d."DeliveryHour") = r."DeliveryHour"
GROUP  BY 1;

This time, selecting all names and returning one row per name like you asked in the comment.

Details for FILTER:

• Return counts for multiple ranges in a single SELECT statement