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"
grouping them all together
, do you mean all selected rows, so that you get a single row aggregating all counts? Your column "Row" is a bit confusing, that's just noise, right? And it's unclear where the columnSource DA
in the result comes from. Does not agree with the query you show.