Return to Answer

replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/

edited Apr 13, 2017 at 12:42

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?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 Return counts for multiple ranges in a single SELECT statement

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

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

clean up

Source Link

edited Feb 26, 2015 at 5:05

Erwin Brandstetter

186.6k
28
465
639

I thinkAfter some processing this boiled down to:

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

SELECT count( sum_price  d."SettlementPointPrice" < 10.5 OR NULL) AS "DA $0  - $10"da_00_10 , count(sum_priced."SettlementPointPrice" >= 10.5 AND sum_priced."SettlementPointPrice" < 20.5 OR NULL) AS "DA $11 - $20"da_11_20 , count(sum_priced."SettlementPointPrice" >= 20.5 AND sum_priced."SettlementPointPrice" < 30.5 OR NULL) AS "DA $21 - $30"da_21_30 FROM ( SELECT sum(d."SettlementPointPrice") AS sum_price 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""DeliveryHour";  GROUP BY

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."SettlementPointPrice", -- really?"SettlementPointName"   , count(*) FILTER (WHERE d."SettlementPointName" "SettlementPointPrice" < 10.5) sub;AS da_00_10

At least that's what your given query implies.

It would make a lot more sense to me without d."SettlementPointPrice" in the GROUP BY clause, though.

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

SELECT count( , count(*) FILTER (WHERE d."SettlementPointPrice" >= 10.5  AND d."SettlementPointPrice" < 10 20.5 OR NULL) AS "DA $0 - $10"da_11_20 , count(*) FILTER (WHERE d."SettlementPointPrice" >= 1020.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"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";"DeliveryHour" GROUP BY 1;

AboutThis time, selecting all names and returning one row per name like you asked in the counting techniquecomment.

Details for FILTER:

For absolute performance, is SUM faster or COUNT?Return counts for multiple ranges in a single SELECT statement

I think this is what you are after:

SELECT count( sum_price < 10.5 OR NULL) AS "DA $0  - $10" , count(sum_price >= 10.5 AND sum_price < 20.5 OR NULL) AS "DA $11 - $20" , count(sum_price >= 20.5 AND sum_price < 30.5 OR NULL) AS "DA $21 - $30" FROM ( SELECT sum(d."SettlementPointPrice") AS sum_price 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"  GROUP BY d."SettlementPointPrice", -- really?   d."SettlementPointName"  ) sub;

At least that's what your given query implies.

It would make a lot more sense to me without d."SettlementPointPrice" in the GROUP BY clause, though.

Or, 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 $0 - $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?

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

added 143 characters in body

Source Link

edited Feb 26, 2015 at 4:54

Erwin Brandstetter

186.6k
28
465
639

I think this is what you are after:

SELECT count( sum_price < 10.5 OR NULL) AS "DA $0 - $10" , count(sum_price >= 10.5 AND sum_price < 20.5 OR NULL) AS "DA $11 - $20" , count(sum_price >= 20.5 AND sum_price < 30.5 OR NULL) AS "DA $21 - $30" FROM ( SELECT sum(d."SettlementPointPrice") AS sum_price 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" GROUP BY d."SettlementPointPrice", -- really? d."SettlementPointName" ) sub;

At least that's what your given query implies.

It would make a lot more sense to me without d."SettlementPointPrice" in the GROUP BY clause, though.

Or, 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 $0 - $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?

I think this is what you are after:

SELECT count( sum_price < 10.5 OR NULL) AS "DA $0 - $10" , count(sum_price >= 10.5 AND sum_price < 20.5 OR NULL) AS "DA $11 - $20" , count(sum_price >= 20.5 AND sum_price < 30.5 OR NULL) AS "DA $21 - $30" FROM ( SELECT sum(d."SettlementPointPrice") AS sum_price 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" GROUP BY d."SettlementPointPrice", -- really? d."SettlementPointName" ) sub;

At least that's what your given query implies.

It would make a lot more sense to me without d."SettlementPointPrice" in the GROUP BY clause, though.

Or, 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 $0 - $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";

I think this is what you are after:

SELECT count( sum_price < 10.5 OR NULL) AS "DA $0 - $10" , count(sum_price >= 10.5 AND sum_price < 20.5 OR NULL) AS "DA $11 - $20" , count(sum_price >= 20.5 AND sum_price < 30.5 OR NULL) AS "DA $21 - $30" FROM ( SELECT sum(d."SettlementPointPrice") AS sum_price 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" GROUP BY d."SettlementPointPrice", -- really? d."SettlementPointName" ) sub;

At least that's what your given query implies.

It would make a lot more sense to me without d."SettlementPointPrice" in the GROUP BY clause, though.

Or, 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 $0 - $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: