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
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
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
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
edited Sep 22 at 11:47
asked Sep 22 at 10:54
ñÃÂsýù÷
1227
1227
add a comment |Â
add a comment |Â
4 Answers
4
active
oldest
votes
up vote
8
down vote
accepted
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áµÂá´¹.
add a comment |Â
up vote
6
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 ;
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...
)
add a comment |Â
up vote
2
down vote
No. Use column aliases.
As best practice, always use column aliases.
add a comment |Â
4 Answers
4
active
oldest
votes
4 Answers
4
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
8
down vote
accepted
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áµÂá´¹.
add a comment |Â
up vote
8
down vote
accepted
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áµÂá´¹.
add a comment |Â
up vote
8
down vote
accepted
up vote
8
down vote
accepted
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áµÂá´¹.
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áµÂá´¹.
edited Sep 24 at 8:12
answered Sep 22 at 12:19
Andriy M
15.4k53471
15.4k53471
add a comment |Â
add a comment |Â
up vote
6
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 ;
add a comment |Â
up vote
6
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 ;
add a comment |Â
up vote
6
down vote
up vote
6
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 ;
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 ;
edited Sep 22 at 11:53
answered Sep 22 at 11:43
ypercubeáµÂá´¹
72.2k11120195
72.2k11120195
add a comment |Â
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...
)
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...
)
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...
)
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...
)
answered Sep 22 at 11:14
Kapil Bhagchandani
15010
15010
add a comment |Â
add a comment |Â
up vote
2
down vote
No. Use column aliases.
As best practice, always use column aliases.
add a comment |Â
up vote
2
down vote
No. Use column aliases.
As best practice, always use column aliases.
add a comment |Â
up vote
2
down vote
up vote
2
down vote
No. Use column aliases.
As best practice, always use column aliases.
No. Use column aliases.
As best practice, always use column aliases.
answered Sep 22 at 11:50
AMtwo
4,074824
4,074824
add a comment |Â
add a comment |Â
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
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
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
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
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