CODEWITHSUNDEEP
sqlcase
Ram
Ram

Reputation: 11

SQL Alternative

Is there a better way to approach this SQL

Sometimes I have income = 0 in certain cases, I need the monthly income divided by the non zero Income in a particular month

SELECT   "CLASS",
         "SCHOOL",
         "Year",
         "MONTH",
         Sum("JanIncome")/Sum(
         CASE
                  WHEN "JanIncome">0 THEN 1
                  ELSE NULL
         END) AS "JanAvg"
         Sum("FebIncome")/Sum(
         CASE
                  WHEN "FebIncome">0 THEN 1
                  ELSE NULL
         END) AS "FebAvg"
FROM     (
                SELECT *
                FROM   school )
GROUP BY "CLASS",
         "SCHOOL",
         "Year",
         "MONTH";

Upvotes: 0

Views: 77

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239724

How about just nulling out the 0s and using the built in AVG:

SELECT "CLASS","SCHOOL","Year","MONTH",
  AVG(NULLIF("JanIncome",0)) AS "JanAvg",
  AVG(NULLIF("FebIncome",0)) as "FebAvg"

AVG ignores nulls.

Upvotes: 2

Related Questions