How to perform group by and order by AFTER union?

How to perform group by and order by AFTER union?

• Eli Robillard

  Thank you Tomik.

  However, your statement will perform like this:

  SELECT * from A ;
  UNION ;
  (SELECT * from B group by 1, 2 order by 1, 2)

  
  

• Steve Powell

  Since the GROUP BY clause in a UNION belongs to the individual tables, I think the only solution, pre-VFP 9, is to use two queries in sequence.
  
  Tamar
  
  

• ChrisLanotte

  Yes,

  I simplified my statement a little bit, maybe that's the reason.

  To say I have 2 order table, one is current orders while the other is history.

  I want to search both tables and group the orders by order month.

  I think the more accurate statement should be like:

  select orderMonth, count (order_no) from (

       select orderMonth, order_no from currentOrder

       union

       select orderMonth, order_no from historyOrder)

  group by orderMonth

  order by orderMonth

  The problem I can't do this twice (one for current, one for history) is that sometimes maybe my history is before Nov. 15th, and my current order table starts from Nov. 15th.  Search table separately will give me two records of Nov. sales. 

  Thank you for pointing out this.

  Scott

   

  
  

• maroy

  Scott,

  select field1, field2 from tableA ;
  union ;
  select field1,field2 from tableB ;
  order by 1,2

  While it looks like it's not what you proposed logically it is IMHO. Having other fields would be a logical error (and not even allowed in VFP9 for example).

   

   

  
  

• Poida

  I think you can use this :

  SELECT * from A ;
  UNION ;
  SELECT * from B ;
  group by 1, 2 ;
  order by 1, 2

   

  
  
  
• How to perform group by and order by AFTER union?