Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In T-SQL I can accumulate the results of two unioned queries using the following syntax:

SELECT Q.a, SUM(Q.b)
FROM (
SELECT f1 as a, f2 as b FROM TBL1
UNION ALL 
SELECT f1 as a, f2 as b FROM TBL2
)
GROUP BY Q.a

What is the equivalent in Oracle?

share|improve this question
3  
What happened when you tried? –  a_horse_with_no_name Apr 2 '13 at 22:25
    
I get this super-helpful error: ORA-00933: SQL command not properly ended –  Daniel Williams Apr 3 '13 at 23:08
add comment

1 Answer

up vote 2 down vote accepted

Assuming that you alias the inline view to Q, that syntax should be perfectly valid in Oracle. I'm assuming that you actually have the alias in the SQL Server version as well.

SQL> ed
Wrote file afiedt.buf

  1  select q.a, sum(q.b)
  2    from (select deptno a, sal b from emp
  3          union all
  4          select 40, 10000 from dual
  5         ) q
  6*  group by q.a
SQL>
SQL> /

         A   SUM(Q.B)
---------- ----------
        30       9406
        20      10880
        40      10000
        10       8753
share|improve this answer
1  
. . Ironically, the posted syntax should work for Oracle (which doesn't require aliases on subqueries), but shouldn't work in SQL Server (which does). –  Gordon Linoff Apr 2 '13 at 22:04
    
Absolutely right, it worked all along. The actual statement was much longer, and in my final Group BY I had a space where a period should have been. But you breaking it down helped. –  Daniel Williams Apr 3 '13 at 23:18
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.