How to SUM a column without having its name

oraclesum

I'm stuck in a way that how to do sum on a column let's say first column of a table (result from another query) without knowing the column name with something like column position id.

it's something like this

select sum(what?), employID from
    ( select count(*), employID from  table1...
           union all
      select count(*), employID from  table2...
           union all
      select count(*), employID from  table3...
)

Or if it's in single query (single simple select query with using sum() ) like:

select employName, sum(what?), employID from tableX

How do I tell SUM() function to sum based on column position index in table like SUM(2)?

Note: I don't want to use column alias, any possibility of doing SUM not based on column name?

I know I can use column-name or aliases, but I really want to know the possibility of not using only these which is why I'm asking this question, if no possible ways, I will accept "No" as correct answer then.

Best Answer

Many (perhaps, most) other database products will not allow you to define a derived table with a calculated column that has no explicitly assigned alias. In other words, they will not let you have this

SELECT
  ...
FROM
  (
    SELECT
      expression
    FROM
      ...
  )

Instead, you would have to do something like this:

SELECT
  ...
FROM
  (
    SELECT
      expression AS SomeAlias
    FROM
      ...
  )

The reason is, they do not assign a name to an expression automatically. In contrast, Oracle does do that, and that is why Oracle is fine when your derived table has a calculated column without an explicit alias. That column will have a default name. The only problem is, what that name will be.

Basically, the name is the expression itself but Oracle applies certain transformations to the original expression before using it as a name. In particular, all unquoted identifiers (including names of functions) are uppercased and all spaces are removed. Thus, if the expression is count(*), the default name of the column becomes COUNT(*). If the expression is a + 1, the default name will be A+1. Note that the presence of special symbols in both those example ((, *, ), +) prescribes that you enclose the name in quotation marks to be able to use it as a reference. In other words, if you have a derived table like this:

  (
    SELECT
      COUNT(*)
    FROM
      ...
  )

then you must write

SELECT
  "COUNT(*)"
FROM
  (
    SELECT
      COUNT(*)
    FROM
      ...
  )

rather than

SELECT
  COUNT(*)
FROM
  (
    SELECT
      COUNT(*)
    FROM
      ...
  )

if you want to reference the derived table's column; otherwise Oracle will be interpreting the reference not as a reference but as an actual expression (another call of the COUNT function, this time applied to the output of the derived table).

So, in your specific example, you can reference the derived table's first column as "COUNT(*)":

select sum("COUNT(*)"), employID from
    ( select count(*), employID from  table1...
           union all
      select count(*), employID from  table2...
           union all
      select count(*), employID from  table3...
)

That said, why avoid assigning an alias explicitly? Often it is not about how you want to do things but rather about how things are meant to be done. Besides, if using AS name immediately after the expression is for some reason unacceptable, you have other options of assigning a column alias, as suggested in the answer by ypercubeᵀᴹ.

Related Question