CODEWITHSUNDEEP
mysql
Brayn
Brayn

Reputation: 416

Make a calculation with aliases in one (sub)query

I have a table with data and am trying to make the following calculation in a query: IDO / IDP * 100.

**TBL**
Name    IDP    IDO
ABC     123    231
DEF     124    NULL
GHI     125    NULL
JKL     126    342
KNM     127    NULL

I managed to count up all the values needed to make the calculation and gave each an alias. This is what I have so far, but the calculation part returns an error telling me the references are not supported.

SELECT
    SUM(case when IDP is not null then 1 end) as Checked,
    SUM(case when IDO is null then 1 end) as NotChecked,
        (SELECT Checked / NotChecked) * 100 AS Result
FROM TBL

I've also tried with a subquery, but it results in the same error.

SELECT
        SUM(case when IDP is not null then 1 end) as Checked,
        SUM(case when IDO is null then 1 end) as NotChecked,
            (
              SELECT Checked / NotChecked * 100 AS Result FROM TBL
            )
        FROM TBL

What would be the right or a better way to do this?

Upvotes: 1

Views: 61

Answers (1)

EdmCoff
EdmCoff

Reputation: 3576

I don't think you will be able to reference aliases created in a SELECT clause in that same SELECT clause.

As you probably know, you could just repeat the logic, like the following (although I assume the purpose of your question is to avoid writing the logic twice).

SELECT
    SUM(case when IDP is not null then 1 end) as Checked,
    SUM(case when IDO is null then 1 end) as NotChecked,
        (SUM(case when IDP is not null then 1 end) / SUM(case when IDO is null then 1 end)) * 100 AS Result
FROM TBL

A subquery version, where you don't repeat the logic, would be the following. Note that the aliases are created in the subquery so the outer query has no trouble referencing them.

SELECT
 Checked, NotChecked, (Checked / NotChecked) * 100 as Result
FROM
(
 SELECT
    SUM(case when IDP is not null then 1 end) as Checked,
    SUM(case when IDO is null then 1 end) as NotChecked
 FROM TBL 
) tt

Since you don't seem to be summing, just counting null/non-null values, I personally find the sum/case statements a bit confusing and might prefer something like this:

SELECT
 Checked, NotChecked, (Checked / NotChecked) * 100 as Result
FROM
(
 SELECT
    count(IDP) as Checked,
    count(*)-count(IDO) as NotChecked
 FROM TBL 
) tt

Upvotes: 2

Related Questions