Problem solve Get help with specific problems with your technologies, process and projects.

SQL UNION to get subtotals

Interactive SQL is great for a quick display of record counts using the GROUP BY clause, but with a little more effort, you can also display subtotals at the same time using UNION. This technique combines multiple SELECT statements, and is therefore somewhat inefficient, but it's great for geting a count of stores by district, for example.

The first SELECT is a GROUP BY at the lowest level you want. The '999' constants are used for placing the subtotals at the end of their group.

Some of the requirements:                                         
   -- Same number of fields in all selects (you can use spaces or digits 
      to reserve the  space)                                 
   -- Corresponding fields must be alike                              
   -- Specify order-by on last select only

SELECT Company, Region, District, '  ', COUNT(*)
  FROM StoreTbl                               
  GROUP BY  Company, Region, District      
  UNION SELECT Company, Region,  999,	
   'Region ' || DIGITS(Region),  COUNT(*)   
    FROM StoreTbl                             
    GROUP BY Company, Region                  
      UNION SELECT Company, 999              	
        999, 'Company' || Company, count(*)     
        from StoreTbl                         
        group by Company                      
        order by Company

Dig Deeper on RPG iSeries programming

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.