In Oracle, a query like this will work fine:
SELECT num_occurrences, COUNT(*)
FROM (SELECT xxxx, COUNT(*) num_occurrences
FROM mytable
GROUP BY xxxx)
GROUP BY num_occurrences
In Teradata (v 12 as least), one must alias the nested query like this:
SELECT num_occurrences, COUNT(*)
FROM (SELECT xxxx, COUNT(*) num_occurrences
FROM mytable
GROUP BY xxxx)
GROUP BY num_occurrences
In Teradata (v 12 as least), one must alias the nested query like this:
SELECT num_occurrences, COUNT(*)
FROM (SELECT xxxx, COUNT(*) num_occurrences
FROM mytable
GROUP BY xxxx) a
GROUP BY num_occurrences
If not you get a message like this ...
3707: Syntax error, expected something like a name or a Unicode delimited identifier or an 'UDFCALLNAME' keyword between ')' and the 'group' keyword
To answer Christi/Christophe's question in the comment below ...
select count(*) from
(select ctm_job, count(*)
from BCRTEC.x_ctm_job
group by ctm_job
having count(*) > 1);
I think this should just work.
Try this ... (note column alias for count(*) mycount in the inner query and the subquery alias a highlighted in red below) ...
select count(*) from
(select ctm_job, count(*) mycount
from BCRTEC.x_ctm_job
group by ctm_job
having count(*) > 1) a
select count(*) from
(select ctm_job, count(*)
from BCRTEC.x_ctm_job
group by ctm_job
having count(*) > 1);
I think this should just work.
Try this ... (note column alias for count(*) mycount in the inner query and the subquery alias a highlighted in red below) ...
select count(*) from
(select ctm_job, count(*) mycount
from BCRTEC.x_ctm_job
group by ctm_job
having count(*) > 1) a
7 comments:
Thank you, thank you, thank you!!!
you save my weekend !!! thanks Heros
What about for the following query:
select count(*) from
(select ctm_job, count(*)
from BCRTEC.x_ctm_job
group by ctm_job
having count(*) > 1);?
I tried to alias but it did not work.
What about the following similar query:
select count(*) from
(select ctm_job, count(*)
from ${BCRTEC}.x_ctm_job
group by ctm_job
having count(*) > 1);?
I tried to alias it as you did but it did not work.
Thanks a lot!!!
Thank you, Guy! This time it worked for me. :)
thanks!!!!!!
Post a Comment