CODEWITHSUNDEEP
sqloracle-database
user4229322
user4229322

Reputation:

Oracle SQL division

I have 

SELECT 
COUNT(*) AS a,
SUM(CASE WHEN r.hn IS NOT NULL THEN 1 ELSE 0 END) AS b,
SUM(CASE WHEN r.hn IS NULL THEN 1 ELSE 0 END) AS c,
( ____ / ____ ) AS d
FROM
x
LEFT JOIN (SELECT DISTINCT xn FROM yn) r ON x.xn = y.xn;

I need the blanks on line 4 to be the values set to 'a' and 'c', I'm not sure what the correct syntax is though.

Upvotes: 3

Views: 17421

Answers (2)

Alex Poole
Alex Poole

Reputation: 191275

You can't refer to column aliases in the same level of the query (except in the order by clause), so you have to either repeat the original expression as in @juergend's answer, or use an inline view:

SELECT a, b, c, a/c AS d
FROM (
  SELECT 
  COUNT(*) AS a,
  SUM(CASE WHEN y.hn IS NOT NULL THEN 1 ELSE 0 END) AS b,
  SUM(CASE WHEN y.hn IS NULL THEN 1 ELSE 0 END) AS c
  FROM x
  LEFT JOIN (SELECT DISTINCT xn FROM yn) y ON y.xn = x.xn
);

For complicated expressions this is a bit simpler and easier to maintain - if the expression changes you only have to modify it in one place, reducing the risk of a mistake.

If you're trying to make d the ratio of nulls to the total then you just need the division reversed, as c/a; and if you wanted the percentage then100*c/a, possibly rounded or truncated to a certain precision.

And as Clockwork-Muse mentioned, since count() ignores nulls, you coudl use that instead of the two sum() calls:

SELECT a, b, c, a/c AS d
FROM (
  SELECT 
  COUNT(*) AS a,
  COUNT(y.hn) AS b,
  COUNT(*) - COUNT(y.hn) AS c
  FROM x
  LEFT JOIN (SELECT DISTINCT xn FROM yn) y ON y.xn = x.xn
);

... or you could calculate c in the outer query too, as (b - a), though that makes the d calculation messier.

Upvotes: 2

juergen d
juergen d

Reputation: 204766

The correct syntax is to rewrite the statements again. You can't re-use alias names in the select clause.

SELECT COUNT(*) AS t,
       count(r.hn) AS c,
       SUM(case when r.hn IS NULL then 1 end) AS u,
       count(r.hn) / SUM(case when r.hn IS NULL then 1 end) AS p
FROM h
LEFT JOIN (SELECT DISTINCT hn FROM r) r ON h.hn = r.hn;

Upvotes: 1

Related Questions