RISE to Bloome Software
Log In    
r2bsoftware.se r2bsoftware.se
Click to hide navigation tree

Using aggregate functions

The RISE view editor supports group by and aggregated functions. Use this, for instance, to accomplish views that calculates MIN, MAX, SUM etc for a set of of instances (rows) or to COUNT the number of instances that meet some criteria.

Create a view by dropping it from the left-hand toolbar onto your base (FROM) entity or open an existing view. In the view editor, drag entities, views and attributes from the left-hand tree to make include them in your view. Then apply the aggregate functions you need by selecting the attribute and clicking the Function-link. It's a good idea to rename the aggregated attribute as well, to reflect result (ChildID to NumberOfChildren in example below).  Drag the attributes needed for the group to the "Group by"-box and delete all other attributes, if any. To use aggregate functions in a view all attributes must either be part of the GROUP BY list or have aggregate functions applied on them, i.e. you can't leave some attributes in the right-hand tree unattended. 

The above view settings will result in SQL/DDL code similar to the below MySQL code (loop is the model prefix).

CREATE VIEW "v_loop_ParentChildren" ("ID", "NumberOfChildren") 
  SELECT "t"."c_id", COUNT("Child"."c_id") 
  FROM "t_loop_u_Parent" "t" 
  LEFT OUTER JOIN "t_loop_u_Child" "Child" 
    ON ("Child"."c_r_Parent" = "t"."c_id") 
  GROUP BY "t"."c_id";