title: UNION of two queries with GROUP/ORDER BY results in ORA-00933: SQL command not properly ended
body: <p>I am trying to combine two SQL queries into one using a UNION clause. However, I am somehow stuck with the error message <code>ORA-00933: SQL command not properly ended</code> on the line of the UNION operator.</p>
  
  <p>The only difference between the two queries is an additional check in the WHERE clause in one of the statements and different <code>b.typeOf</code> values being queried. The rest is 100% identical. </p>
  
  <p>I don't really understand why Oracle expects the SQL command to end right before the UNION operator, I mean the only reason these operators exist is to combine  queries... Why would it expect the query to end before combining it with the following statement :/</p>
  
  <p>The only thing I can think of is that it is having problems with the GROUP BY and/or ORDER BY clauses in the statements. However, these are vital, so I can't just remove them and try it again, as the data wouldn't make any sense without it.</p>
  
  <p>Does anyone know an explanation for this behavior and a possible fix for it? Trying to locate similar problems on SO or Google didn't yield much yet unfortunately. Any hints are appreciated.</p>
  
  <p>Here's my SQL:</p>
  
  <pre><code>SELECT 'Test' as Name,
  extract(Year FROM a.datum) AS Jahr, extract(Month FROM a.datum) AS Monat, count(a.datum) AS  Anzahl_Tickets 
  FROM table1 a, table2 b, table2 c 
  WHERE a.fk_ID = b.id and b.fk_ID = c.id and b.typeOf in (3,4) 
  and (a.isXYZ = 0 or a.isXYZ is Null or a.ZYX &gt; 0) 
  and mod(a.fk_id,2) = 1
  and ...
  and ...
  and ... 
  GROUP BY extract(Year FROM a.datum), extract(Month FROM a.datum)
  ORDER BY extract(Year FROM a.datum), extract(Month FROM a.datum)
  
  UNION
  
  SELECT 'Test' as Name, 
  extract(Year FROM a.datum) AS Jahr, extract(Month FROM a.datum) AS Monat, count(a.datum) AS Anzahl_Tickets 
  FROM table1 a, table2 b, table2 c 
  WHERE a.fk_ID = b.id and b.fk_ID = c.id and b.typeOf in (0,1) 
  and (a.isXYZ = 0 or a.isXYZ is Null or a.ZYX &gt; 0)
  -- Notice missing modulo calculation. Thats basically the whole point of splitting this into 2 separate queries.
  and ...
  and ...
  and ... 
  GROUP BY extract(Year FROM a.datum), extract(Month FROM a.datum) 
  ORDER BY extract(Year FROM a.datum), extract(Month FROM a.datum);  
  </code></pre>
  
  <p>Edit: A new problem now that the UNION works. It doesn't show the results like I intended:</p>
  
  <p><img src="https://i.stack.imgur.com/ZDx6q.png" alt="http://i.imgur.com/cBQpbFv.png"></p>
  
  <p>Instead of 4 columns I actually expexted 2. Why it doesn't sum 371 + 2 and 1 + 401 into one dataset each?</p>
  
  <p>Edit 2: Nevermind, fixed it by changing the outer clause to:</p>
  
  <pre><code>SELECT Name, JAHR, MONAT, SUM(Anzahl_Tickets) FROM 
  (
  ....
  ) GROUP BY Name, Jahr, Monat ORDER BY Jahr, Monat;
  </code></pre>