SQL Case Statement – Calculating Margin in CASE Statement and GROUP BY Clause

aggregatecasegroup by

I think I am running into an error in how I have chosen to aggregate my columns in a group by statement.

I am working out Margin % and grouping into months using a case statement like so:

SUM(CASE WHEN MONTH(DateProcessed) = 1 AND Revenus <> 0 THEN (Revenue - Cost) / Revenue ELSE 0 END AS [January]

So my equation ends up as: SUM( (Revenue - Cost) / Revenue )

Which in months with multiple transactions gives me the wrong value

I am sure my equation needs to be: SUM(Revenue - Cost) / SUM(Revenue)

But I cannot format my case statement like that. How would you work out this sort of equation?

Thanks,

Best Answer

I don't have your full schema, but this should work for you.

;WITH CTE_Summary AS
(
    SELECT MONTH(DateProcessed) AS MonthProcessed
        , SUM(ISNULL(Revenue, 0.0)) AS RevenueSum
        , SUM(ISNULL(Cost, 0.0)) AS CostSum
    FROM <TABLE NAME>
    GROUP BY MONTH(DateProcessed)
)
SELECT sum(CASE WHEN MonthProcessed = 1 THEN (RevenueSum - CostSum) / RevenueSum ELSE 0.0 END) AS [January]
FROM CTE_Summary

Added the sum function.

Related Solutions

ORA-00937 – Calculating Sub-Value in Aggregate SELECT Statement

Your additional calculated value:

(select count(t1.num) from T t1)

Is a scalar subquery, which is a dynamic rather than static expression. As such it's treated the same as a column as far as the aggregate is concerned and needs to be included in the group by clause to avoid the ORA-00937: not a single-group group function error

However, oracle does not allow subqueries as part of the group by clause and trying to include the scalar subquery and/or the whole case statement:

group by (case (select count(*) cnt from t t1) when 0 then 1 else 0 end)

just results in an ORA-22818: subquery expressions not allowed here error.

The only ways around this are to either convert your scalar subquery to an aggregate value like so:

max(case (select count(*) cnt from t t1) when 0 then 1 else 0 end)

(case max((select count(*) cnt from t t1)) when 0 then 1 else 0 end)

or rewrite your query to move the unaggregated scalar subquery out of the aggregated query:

select (case (select count(*) cnt from t t1) when 0 then 1 else 0 end) * sum
  from (select sum(t3.num) sum from t t3) t2;

or precompute your scalar subquery so it can be used in the group by clause:

select case t1.cnt when 0 then 1 else 0 end * sum(t2.num)
  from t t2
     , (select count(*) cnt from t) t1
 group by case t1.cnt when 0 then 1 else 0 end

PostgreSQL – Using Nested WHEN Aggregate Functions

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

Best Answer

Related Solutions

ORA-00937 – Calculating Sub-Value in Aggregate SELECT Statement

PostgreSQL – Using Nested WHEN Aggregate Functions

Related Question