How do you SUM a column without having its name?

Clash Royale CLAN TAG#URR8PPP

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;

up vote
1
down vote

favorite

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.

oracle sum

share|improve this question

edited 55 mins ago

asked 1 hour ago

αғsнιη

1177

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Use a column alias.
  – Michael Kutz
  1 hour ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Any particular reason for not using column name in second part of your question, where you are pointing towards single query?
  – Kapil Bhagchandani
  1 hour ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Isn't this a bit like asking for directions, person responds "where to" and then you say "I don't know"? How can you not know the table and/or column names?
  – Vérace
  24 mins ago
  

  
  
  
  

add a comment | 

up vote
1
down vote

favorite

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.

oracle sum

share|improve this question

edited 55 mins ago

asked 1 hour ago

αғsнιη

1177

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Use a column alias.
  – Michael Kutz
  1 hour ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Any particular reason for not using column name in second part of your question, where you are pointing towards single query?
  – Kapil Bhagchandani
  1 hour ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Isn't this a bit like asking for directions, person responds "where to" and then you say "I don't know"? How can you not know the table and/or column names?
  – Vérace
  24 mins ago
  

  
  
  
  

add a comment | 

up vote
1
down vote

favorite

up vote
1
down vote

favorite

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.

oracle sum

share|improve this question

edited 55 mins ago

asked 1 hour ago

αғsнιη

1177

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.

oracle sum

oracle sum

share|improve this question

edited 55 mins ago

asked 1 hour ago

αғsнιη

1177

share|improve this question

edited 55 mins ago

asked 1 hour ago

αғsнιη

1177

share|improve this question

share|improve this question

edited 55 mins ago

edited 55 mins ago

edited 55 mins ago

asked 1 hour ago

αғsнιη

1177

asked 1 hour ago

αғsнιη

1177

asked 1 hour ago

αғsнιη

1177

1177

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Use a column alias.
  – Michael Kutz
  1 hour ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Any particular reason for not using column name in second part of your question, where you are pointing towards single query?
  – Kapil Bhagchandani
  1 hour ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Isn't this a bit like asking for directions, person responds "where to" and then you say "I don't know"? How can you not know the table and/or column names?
  – Vérace
  24 mins ago
  

  
  
  
  

add a comment | 

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Use a column alias.
  – Michael Kutz
  1 hour ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Any particular reason for not using column name in second part of your question, where you are pointing towards single query?
  – Kapil Bhagchandani
  1 hour ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Isn't this a bit like asking for directions, person responds "where to" and then you say "I don't know"? How can you not know the table and/or column names?
  – Vérace
  24 mins ago
  

  
  
  
  

Use a column alias.
– Michael Kutz
1 hour ago

Use a column alias.
– Michael Kutz
1 hour ago

Any particular reason for not using column name in second part of your question, where you are pointing towards single query?
– Kapil Bhagchandani
1 hour ago

Any particular reason for not using column name in second part of your question, where you are pointing towards single query?
– Kapil Bhagchandani
1 hour ago

Isn't this a bit like asking for directions, person responds "where to" and then you say "I don't know"? How can you not know the table and/or column names?
– Vérace
24 mins ago

Isn't this a bit like asking for directions, person responds "where to" and then you say "I don't know"? How can you not know the table and/or column names?
– Vérace
24 mins ago

add a comment | 

4 Answers
4

active

oldest

votes

up vote
2
down vote

You need to rewrite your query by making use of aliases as

select sum(employees), employID from
 ( select count(*) As employees, employID from table1...
 union all
 select count(*) As employees, employID from table2...
 union all
 select count(*) As employees, employID from table3...
)

share|improve this answer

answered 1 hour ago

Kapil Bhagchandani

1509

• 
  
  
  

  
  

  
  
  
  
  
  

  
  thank you for the answer. I just edited my question that I don't want to use aliases. anyway what you will do in single query (single select statement with using the sum() )?
  – Î±Ò“sнιη
  1 hour ago
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Can you please update your question to specify the use case, for not using the column name and alias.
  – Kapil Bhagchandani
  1 hour ago
  

  
  
  
  

add a comment | 

up vote
1
down vote

No. Use column aliases.

As best practice, always use column aliases.

share|improve this answer

answered 52 mins ago

AMtwo

3,979824

add a comment | 

up vote
1
down vote

Another way to add aliases (for derived tables and their columns) in standard SQL would be (unfortunately it hasn't been implemented in Oracle):

select 
 sum(count_employees) as total_count,
 employID 
from
 ( select count(*), employID from table1...
 union all
 select count(*), employID from table2...
 union all
 select count(*), employID from table3...
 )
 t (count_employees, employID)
 -- table_alias (column1_alias, column2_alias)
group by
 employID ;

You can use a similar construction though, a CTE, that works in recent versions of Oracle (11+):

with t (count_employees, employID)
 -- table_alias (column1_alias, column2_alias)
 as
 ( select count(*), employID from table1...
 union all
 select count(*), employID from table2...
 union all
 select count(*), employID from table3...
 )
select 
 sum(count_employees) as total_count,
 employID 
from
 t
group by
 employID ;

share|improve this answer

edited 49 mins ago

answered 58 mins ago

ypercubeᵀᴹ

71.8k11119194

add a comment | 

up vote
0
down vote

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, you cannot have this

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

Instead, you should 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...
)

But really, 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 assigning an alias inline (using AS name immediately after the expression) is for some reason unacceptable, you have other options for doing that, as suggested in the answer by ypercubeᵀᴹ.

share|improve this answer

answered 23 mins ago

Andriy M

15.3k53470

add a comment | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: false,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

 

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f218315%2fhow-do-you-sum-a-column-without-having-its-name%23new-answer', 'question_page');

);

Post as a guest

Name Email

4 Answers
4

active

oldest

votes

4 Answers
4

active

oldest

votes

active

oldest

votes

active

oldest

votes

up vote
2
down vote

You need to rewrite your query by making use of aliases as

select sum(employees), employID from
 ( select count(*) As employees, employID from table1...
 union all
 select count(*) As employees, employID from table2...
 union all
 select count(*) As employees, employID from table3...
)

share|improve this answer

answered 1 hour ago

Kapil Bhagchandani

1509

• 
  
  
  

  
  

  
  
  
  
  
  

  
  thank you for the answer. I just edited my question that I don't want to use aliases. anyway what you will do in single query (single select statement with using the sum() )?
  – Î±Ò“sнιη
  1 hour ago
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Can you please update your question to specify the use case, for not using the column name and alias.
  – Kapil Bhagchandani
  1 hour ago
  

  
  
  
  

add a comment | 

up vote
2
down vote

You need to rewrite your query by making use of aliases as

select sum(employees), employID from
 ( select count(*) As employees, employID from table1...
 union all
 select count(*) As employees, employID from table2...
 union all
 select count(*) As employees, employID from table3...
)

share|improve this answer

answered 1 hour ago

Kapil Bhagchandani

1509

• 
  
  
  

  
  

  
  
  
  
  
  

  
  thank you for the answer. I just edited my question that I don't want to use aliases. anyway what you will do in single query (single select statement with using the sum() )?
  – Î±Ò“sнιη
  1 hour ago
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Can you please update your question to specify the use case, for not using the column name and alias.
  – Kapil Bhagchandani
  1 hour ago
  

  
  
  
  

add a comment | 

up vote
2
down vote

up vote
2
down vote

You need to rewrite your query by making use of aliases as

select sum(employees), employID from
 ( select count(*) As employees, employID from table1...
 union all
 select count(*) As employees, employID from table2...
 union all
 select count(*) As employees, employID from table3...
)

share|improve this answer

answered 1 hour ago

Kapil Bhagchandani

1509

You need to rewrite your query by making use of aliases as

select sum(employees), employID from
 ( select count(*) As employees, employID from table1...
 union all
 select count(*) As employees, employID from table2...
 union all
 select count(*) As employees, employID from table3...
)

share|improve this answer

answered 1 hour ago

Kapil Bhagchandani

1509

share|improve this answer

share|improve this answer

answered 1 hour ago

Kapil Bhagchandani

1509

answered 1 hour ago

Kapil Bhagchandani

1509

answered 1 hour ago

Kapil Bhagchandani

1509

1509

• 
  
  
  

  
  

  
  
  
  
  
  

  
  thank you for the answer. I just edited my question that I don't want to use aliases. anyway what you will do in single query (single select statement with using the sum() )?
  – Î±Ò“sнιη
  1 hour ago
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Can you please update your question to specify the use case, for not using the column name and alias.
  – Kapil Bhagchandani
  1 hour ago
  

  
  
  
  

add a comment | 

• 
  
  
  

  
  

  
  
  
  
  
  

  
  thank you for the answer. I just edited my question that I don't want to use aliases. anyway what you will do in single query (single select statement with using the sum() )?
  – Î±Ò“sнιη
  1 hour ago
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Can you please update your question to specify the use case, for not using the column name and alias.
  – Kapil Bhagchandani
  1 hour ago
  

  
  
  
  

thank you for the answer. I just edited my question that I don't want to use aliases. anyway what you will do in single query (single select statement with using the sum() )?
– Î±Ò“sнιη
1 hour ago

thank you for the answer. I just edited my question that I don't want to use aliases. anyway what you will do in single query (single select statement with using the sum() )?
– Î±Ò“sнιη
1 hour ago

Can you please update your question to specify the use case, for not using the column name and alias.
– Kapil Bhagchandani
1 hour ago

Can you please update your question to specify the use case, for not using the column name and alias.
– Kapil Bhagchandani
1 hour ago

add a comment | 

up vote
1
down vote

No. Use column aliases.

As best practice, always use column aliases.

share|improve this answer

answered 52 mins ago

AMtwo

3,979824

add a comment | 

up vote
1
down vote

No. Use column aliases.

As best practice, always use column aliases.

share|improve this answer

answered 52 mins ago

AMtwo

3,979824

add a comment | 

up vote
1
down vote

up vote
1
down vote

No. Use column aliases.

As best practice, always use column aliases.

share|improve this answer

answered 52 mins ago

AMtwo

3,979824

No. Use column aliases.

As best practice, always use column aliases.

share|improve this answer

answered 52 mins ago

AMtwo

3,979824

share|improve this answer

share|improve this answer

answered 52 mins ago

AMtwo

3,979824

answered 52 mins ago

AMtwo

3,979824

answered 52 mins ago

AMtwo

3,979824

3,979824

add a comment | 

add a comment | 

up vote
1
down vote

Another way to add aliases (for derived tables and their columns) in standard SQL would be (unfortunately it hasn't been implemented in Oracle):

select 
 sum(count_employees) as total_count,
 employID 
from
 ( select count(*), employID from table1...
 union all
 select count(*), employID from table2...
 union all
 select count(*), employID from table3...
 )
 t (count_employees, employID)
 -- table_alias (column1_alias, column2_alias)
group by
 employID ;

You can use a similar construction though, a CTE, that works in recent versions of Oracle (11+):

with t (count_employees, employID)
 -- table_alias (column1_alias, column2_alias)
 as
 ( select count(*), employID from table1...
 union all
 select count(*), employID from table2...
 union all
 select count(*), employID from table3...
 )
select 
 sum(count_employees) as total_count,
 employID 
from
 t
group by
 employID ;

share|improve this answer

edited 49 mins ago

answered 58 mins ago

ypercubeᵀᴹ

71.8k11119194

add a comment | 

up vote
1
down vote

Another way to add aliases (for derived tables and their columns) in standard SQL would be (unfortunately it hasn't been implemented in Oracle):

select 
 sum(count_employees) as total_count,
 employID 
from
 ( select count(*), employID from table1...
 union all
 select count(*), employID from table2...
 union all
 select count(*), employID from table3...
 )
 t (count_employees, employID)
 -- table_alias (column1_alias, column2_alias)
group by
 employID ;

You can use a similar construction though, a CTE, that works in recent versions of Oracle (11+):

with t (count_employees, employID)
 -- table_alias (column1_alias, column2_alias)
 as
 ( select count(*), employID from table1...
 union all
 select count(*), employID from table2...
 union all
 select count(*), employID from table3...
 )
select 
 sum(count_employees) as total_count,
 employID 
from
 t
group by
 employID ;

share|improve this answer

edited 49 mins ago

answered 58 mins ago

ypercubeᵀᴹ

71.8k11119194

add a comment | 

up vote
1
down vote

up vote
1
down vote

Another way to add aliases (for derived tables and their columns) in standard SQL would be (unfortunately it hasn't been implemented in Oracle):

select 
 sum(count_employees) as total_count,
 employID 
from
 ( select count(*), employID from table1...
 union all
 select count(*), employID from table2...
 union all
 select count(*), employID from table3...
 )
 t (count_employees, employID)
 -- table_alias (column1_alias, column2_alias)
group by
 employID ;

You can use a similar construction though, a CTE, that works in recent versions of Oracle (11+):

with t (count_employees, employID)
 -- table_alias (column1_alias, column2_alias)
 as
 ( select count(*), employID from table1...
 union all
 select count(*), employID from table2...
 union all
 select count(*), employID from table3...
 )
select 
 sum(count_employees) as total_count,
 employID 
from
 t
group by
 employID ;

share|improve this answer

edited 49 mins ago

answered 58 mins ago

ypercubeᵀᴹ

71.8k11119194

Another way to add aliases (for derived tables and their columns) in standard SQL would be (unfortunately it hasn't been implemented in Oracle):

select 
 sum(count_employees) as total_count,
 employID 
from
 ( select count(*), employID from table1...
 union all
 select count(*), employID from table2...
 union all
 select count(*), employID from table3...
 )
 t (count_employees, employID)
 -- table_alias (column1_alias, column2_alias)
group by
 employID ;

You can use a similar construction though, a CTE, that works in recent versions of Oracle (11+):

with t (count_employees, employID)
 -- table_alias (column1_alias, column2_alias)
 as
 ( select count(*), employID from table1...
 union all
 select count(*), employID from table2...
 union all
 select count(*), employID from table3...
 )
select 
 sum(count_employees) as total_count,
 employID 
from
 t
group by
 employID ;

share|improve this answer

edited 49 mins ago

answered 58 mins ago

ypercubeᵀᴹ

71.8k11119194

share|improve this answer

share|improve this answer

edited 49 mins ago

edited 49 mins ago

edited 49 mins ago

answered 58 mins ago

ypercubeᵀᴹ

71.8k11119194

answered 58 mins ago

ypercubeᵀᴹ

71.8k11119194

answered 58 mins ago

ypercubeᵀᴹ

71.8k11119194

71.8k11119194

add a comment | 

add a comment | 

up vote
0
down vote

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, you cannot have this

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

Instead, you should 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...
)

But really, 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 assigning an alias inline (using AS name immediately after the expression) is for some reason unacceptable, you have other options for doing that, as suggested in the answer by ypercubeᵀᴹ.

share|improve this answer

answered 23 mins ago

Andriy M

15.3k53470

add a comment | 

up vote
0
down vote

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, you cannot have this

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

Instead, you should 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...
)

But really, 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 assigning an alias inline (using AS name immediately after the expression) is for some reason unacceptable, you have other options for doing that, as suggested in the answer by ypercubeᵀᴹ.

share|improve this answer

answered 23 mins ago

Andriy M

15.3k53470

add a comment | 

up vote
0
down vote

up vote
0
down vote

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, you cannot have this

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

Instead, you should 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...
)

But really, 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 assigning an alias inline (using AS name immediately after the expression) is for some reason unacceptable, you have other options for doing that, as suggested in the answer by ypercubeᵀᴹ.

share|improve this answer

answered 23 mins ago

Andriy M

15.3k53470

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, you cannot have this

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

Instead, you should 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...
)

But really, 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 assigning an alias inline (using AS name immediately after the expression) is for some reason unacceptable, you have other options for doing that, as suggested in the answer by ypercubeᵀᴹ.

share|improve this answer

answered 23 mins ago

Andriy M

15.3k53470

share|improve this answer

share|improve this answer

answered 23 mins ago

Andriy M

15.3k53470

answered 23 mins ago

Andriy M

15.3k53470

answered 23 mins ago

Andriy M

15.3k53470

15.3k53470

add a comment | 

add a comment | 

 

draft saved

draft discarded

 

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f218315%2fhow-do-you-sum-a-column-without-having-its-name%23new-answer', 'question_page');

);

Post as a guest

Name Email

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

Name Email

Name

Email

Name Email

Name

Email