Group By Query With Calculated Column

Business Logic Framework - Tutorial 16:

This tutorial shows you how to run queries that use the GROUP BY clause. The business object's simplify these complex queries and you can continue to rely on the consistent way of retrieving records.

To run a query that groups the results set on one or more fields or performs aggregate functions on columns, we are required to set the ObjectMode property of the business object to ObjectModes.GroupBy. It is important to set the ObjectMode property before creating joins with other objects, which will ensure that all joined objects are included in the result set. Failing to do so can result in unexpected results.

In this tutorial, our purpose is to return a list of customer names along with the total amount of orders placed by each of them. Using a SQL statement we can achieve this result by joining the customer table with orders table, and performing group by on the result set by customer name and enclosing the OrderAmount in SUM aggregate function.

C#
VB.NET

1
// Please note: This tutorial requires that the QO_SHOP sample database included with v 3.x.  

2
// If you have have the the database schema from a previous version of QO_SHOP please upgrade the  

3
// QO_SHOP database by using the Drop Objects.sql and Create Objects.sql. If you like the default  

4
// test data to be automatically inserted, please use the Insert Data.sql file.  

5
 

6
// The purpose of this tutorial is to display the ability to run Group By based SQL Statements without having to  

7
// hard code any element of the statement. Use the fully strongly typed object model, and the Quick Objects will  

8
// take care of the rest.  

9
 

10
// In this tutorial we will return a list of Customer names along with their total order amount.  

11
using (Customers customer = new Customers())  

12
{  

13
 

14
    // We are going to add a calculated field that will concatenate FirstName and LastName values to create a Name column.  

15
    // Lets check what database type is being used and assign the appropriate expression.  

16
    // If you know your database type for certain then you don't need to perform this check and you can directly  

17
    // assign the expression. Since this sample can be used with Oracle as well we are specifying  

18
    // both expressions to ensure compatibility.  

19
    string expression = String.Empty;  

20
    if (customer.DBType == Akal.QuickObjects.DBLibrary.DatabaseObject.DatabaseTypes.SqlServer2000  

21
        ||  

22
        customer.DBType == Akal.QuickObjects.DBLibrary.DatabaseObject.DatabaseTypes.SqlServer2005  

23
        )  

24
    {  

25
        // in SQL Server a + sign is used to concatenate two string values.  

26
        expression = " + ' ' + ";  

27
    }  

28
    else 

29
    {  

30
        // in Oracle a || symbols are used to concatenate two string values.  

31
        expression = "|| ' ' ||";  

32
    }  

33
    // We can use the GetObjectName method of the Utilities to get a database representation of the FirstName field.  

34
    // This will ensure that we have not hard coded the underlying name and keeping everything strongly typed.  

35
    // Also, this method call will enclose the object name as necessary based on the database type.  

36
    expression = "(" 

37
        + Utilities.GetObjectName(customer.FirstName.Name, false, true, customer.DBType)  

38
        + expression +  

39
        Utilities.GetObjectName(customer.LastName.Name, false, true, customer.DBType)  

40
        + ")";  

41
 

42
    // Note: custom/calculated fields do not perform automatic case conversion so if you want your custom  

43
    // column to perform the case conversion you can use any of the alternate lines of code below (commented out).  

44
    //string expression = "UPPER(" + customer.FirstName.Name + " + ' ' + " + customer.LastName.Name + ")";  

45
    //string expression = "LOWER(" + customer.FirstName.Name + " + ' ' + " + customer.LastName.Name + ")";  

46
 

47
    // In version 3.x a new mode called GroupBy has been introduced in the ObjectModes enumeration.  

48
    // By setting the ObjectMode property of the customer object we are letting the customer object know  

49
    // that we want the customer object to created a query based on GROUP BY clause.  

50
    customer.ObjectMode = ObjectModes.GroupBy;  

51
 

52
    // Any objects that are joined to the customer object after the ObjectMode has been set will automatically receive the ObjectMode property value.  

53
    // This will mean that the Orders object's ObjectMode property will change to ObjectModes.GroupBy automatically.  

54
    customer.Join_CustomerID_Orders_Child();  

55
 

56
    // Here we create an instance of a StringField  

57
    // Each Field's constructor has multiple overloads, and we will use the one that accepts three parameters.  

58
    // First parameter is the business object that will contain the field, the second one is the Name of the field.  

59
    // Since we are going to create a custom field we use the Name property of the field as our expression. The last argument  

60
    // is used to specify the alias of the column.  

61
    // For a custom column to work correctly, you must specify a unique value for the ResultSetName property. In our case we specified  

62
    // this value in the constructor.  

63
    StringField sf = new StringField(customer, expression, "Name");  

64
 

65
    // IsCustom property must be set to true.  

66
    sf.IsCustom = true;  

67
 

68
    // Since we want to return order totals for each customer, we must group by the result set by the Name column.  

69
    // To achieve this, simply set the GroupType property to GroupTypes.GroupBy (introduced in v 3.x)  

70
    sf.GroupType = GroupTypes.GroupBy;  

71
 

72
    // We must specify the position of this field in the group by clause. Valid position is 1 or greater.  

73
    // To ensure accurate results, please ensure that position values are unique in all the fields included.  

74
    // GroupPosition value is only required if the GroupType value is set to GroupTypes.GroupBy  

75
    sf.GroupPosition = 1;  

76
 

77
    // To have the result set include order totals for a custom now we need to set the GroupType property of the OrderAmount  

78
    // field to GroupTypes.Sum  

79
    customer.CustomerID_Orders_Child.OrderAmount.GroupType = GroupTypes.Sum;  

80
 

81
    // Define the alias that will be used as the column name in the result set.  

82
    customer.CustomerID_Orders_Child.OrderAmount.ResultSetName.Value = "Order Amount";  

83
 

84
    // Since the OrderAmount field's GroupType property is set to GroupTypes.Sum  

85
    // it is not required that you set the GroupPosition value.  

86
    //customer.CustomerID_Orders_Child.OrderAmount.GroupPosition = 2;  

87
 

88
    customer.Find();  

89
 

90
    // if the Find method returned any records then we will set the Grid View's DataSource and DataMember properties accordingly.  

91
    if (customer.AffectedRecords > 0)  

92
    {  

93
        this.GridView1.DataSource = customer.ResultSet;  

94
        this.GridView1.DataMember = customer.GetResultSetName();  

95
    }  

96
    else 

97
    {  

98
        this.GridView1.DataSource = null;  

99
        this.GridView1.DataMember = null;  

100
    }  

101
}  

102
 

1
'Please note: This tutorial requires that the QO_SHOP sample database included with v 3.x.  

2
'If you have have the the database schema from a previous version of QO_SHOP please upgrade the  

3
'QO_SHOP database by using the Drop Objects.sql and Create Objects.sql. If you like the default  

4
'test data to be automatically inserted, please use the Insert Data.sql file.  

5
 

6
'The purpose of this tutorial is to display the ability to run Group By based SQL Statements without having to  

7
'hard code any element of the statement. Use the fully strongly typed object model, and the Quick Objects will  

8
'take care of the rest.  

9
 

10
'In this tutorial we will return a list of Customer names along with their total order amount.  

11
Using customer As Customers = New Customers()  

12
    ' We are going to add a calculated field that will concatenate FirstName and LastName values to create a Name column.  

13
    ' Lets check what database type is being used and assign the appropriate expression.  

14
    ' If you know your database type for certain then you don't need to perform this check and you can directly  

15
    ' assign the expression. Since this sample can be used with Oracle as well we are specifying  

16
    ' both expressions to ensure compatibility.  

17
    Dim expression As String = String.Empty  

18
    If (customer.DBType = Akal.QuickObjects.DBLibrary.DatabaseObject.DatabaseTypes.SqlServer2000 Or customer.DBType = Akal.QuickObjects.DBLibrary.DatabaseObject.DatabaseTypes.SqlServer2005) Then 

19
        ' in SQL Server a + sign is used to concatenate two string values.  

20
        expression = " + ' ' + " 

21
    Else 

22
        ' in Oracle a || symbols are used to concatenate two string values.  

23
        expression = "|| ' ' ||" 

24
    End If 

25
    ' We can use the GetObjectName method of the Utilities to get a database representation of the FirstName field.  

26
    ' This will ensure that we have not hard coded the underlying name and keeping everything strongly typed.  

27
    ' Also, this method call will enclose the object name as necessary based on the database type.  

28
 

29
    expression = "(" & Utilities.GetObjectName(customer.FirstName.Name, False, True, customer.DBType) & expression & Utilities.GetObjectName(customer.LastName.Name, False, True, customer.DBType) & ")" 

30
 

31
    'Note: custom/calculated fields do not perform automatic case conversion so if you want your custom  

32
    'column to perform the case conversion you can use any of the alternate lines of code below (commented out).  

33
    'string expression = "UPPER(" + customer.FirstName.Name + " + ' ' + " + customer.LastName.Name + ")"  

34
    'string expression = "LOWER(" + customer.FirstName.Name + " + ' ' + " + customer.LastName.Name + ")"  

35
 

36
    'In version 3.x a new mode called GroupBy has been introduced in the ObjectModes enumeration.  

37
    'By setting the ObjectMode property of the customer object we are letting the customer object know  

38
    'that we want the customer object to created a query based on GROUP BY clause.  

39
    customer.ObjectMode = ObjectModes.GroupBy  

40
 

41
 

42
    'Any objects that are joined to the customer object after the ObjectMode has been set will automatically receive the ObjectMode property value.  

43
    'This will mean that the Orders object's ObjectMode property will change to ObjectModes.Grouping automatically.  

44
    customer.Join_CustomerID_Orders_Child()  

45
 

46
    'Here we create an instance of a StringField  

47
    'Each Field's constructor has multiple overloads, and we will use the one that accepts three parameters.  

48
    'First parameter is the business object that will contain the field, the second one is the Name of the field.  

49
    'Since we are going to create a custom field we use the Name property of the field as our expression. The last argument  

50
    'is used to specify the alias of the column.  

51
    'For a custom column to work correctly, you must specify a unique value for the ResultSetName property. In our case we specified  

52
    'this value in the constructor.  

53
    Dim sf As StringField = New StringField(customer, expression, "Name")  

54
 

55
    'IsCustom property must be set to true.  

56
    sf.IsCustom = True 

57
 

58
    'Since we want to return order totals for each customer, we must group by the result set by the Name column.  

59
    'To achieve this, simply set the GroupType property to GroupTypes.GroupBy (introduced in v 3.x)  

60
    sf.GroupType = GroupTypes.GroupBy  

61
 

62
 

63
    'We must specify the position of this field in the group by clause. Valid position is 1 or greater.  

64
    'To ensure accurate results, please ensure that position values are unique in all the fields included.  

65
    'GroupPosition value is only required if the GroupType value is set to GroupTypes.GroupBy  

66
    sf.GroupPosition = 1  

67
 

68
    'To have the result set include order totals for a custom now we need to set the GroupType property of the OrderAmount  

69
    'field to GroupTypes.Sum  

70
    customer.CustomerID_Orders_Child.OrderAmount.GroupType = GroupTypes.Sum  

71
 

72
    ' Define the alias that will be used as the column name in the result set.  

73
    customer.CustomerID_Orders_Child.OrderAmount.ResultSetName.Value = "Order Amount" 

74
    'Since the OrderAmount field's GroupType property is set to GroupTypes.Sum  

75
    'it is not required that you set the GroupPosition value.  

76
    'customer.CustomerID_Orders_Child.OrderAmount.GroupPosition = 2  

77
 

78
    customer.Find()  

79
 

80
    'if the Find method returned any records then we will set the Grid View's DataSource and DataMember properties accordingly.  

81
 

82
    If customer.AffectedRecords > 0 Then 

83
        Me.GridView1.DataSource = customer.ResultSet  

84
        Me.GridView1.DataMember = customer.GetResultSetName()  

85
    Else 

86
        Me.GridView1.DataSource = Nothing 

87
        Me.GridView1.DataMember = Nothing 

88
    End If 

89
End Using  

90
 

Send comments on this topic.

Quick Objects Documentation
Group By Query With Calculated Column

1	// Please note: This tutorial requires that the QO_SHOP sample database included with v 3.x.
2	// If you have have the the database schema from a previous version of QO_SHOP please upgrade the
3	// QO_SHOP database by using the Drop Objects.sql and Create Objects.sql. If you like the default
4	// test data to be automatically inserted, please use the Insert Data.sql file.
5
6	// The purpose of this tutorial is to display the ability to run Group By based SQL Statements without having to
7	// hard code any element of the statement. Use the fully strongly typed object model, and the Quick Objects will
8	// take care of the rest.
9
10	// In this tutorial we will return a list of Customer names along with their total order amount.
11	using (Customers customer = new Customers())
12	{
13
14	// We are going to add a calculated field that will concatenate FirstName and LastName values to create a Name column.
15	// Lets check what database type is being used and assign the appropriate expression.
16	// If you know your database type for certain then you don't need to perform this check and you can directly
17	// assign the expression. Since this sample can be used with Oracle as well we are specifying
18	// both expressions to ensure compatibility.
19	string expression = String.Empty;
20	if (customer.DBType == Akal.QuickObjects.DBLibrary.DatabaseObject.DatabaseTypes.SqlServer2000
21	\|\|
22	customer.DBType == Akal.QuickObjects.DBLibrary.DatabaseObject.DatabaseTypes.SqlServer2005
23	)
24	{
25	// in SQL Server a + sign is used to concatenate two string values.
26	expression = " + ' ' + ";
27	}
28	else
29	{
30	// in Oracle a \|\| symbols are used to concatenate two string values.
31	expression = "\|\| ' ' \|\|";
32	}
33	// We can use the GetObjectName method of the Utilities to get a database representation of the FirstName field.
34	// This will ensure that we have not hard coded the underlying name and keeping everything strongly typed.
35	// Also, this method call will enclose the object name as necessary based on the database type.
36	expression = "("
37	+ Utilities.GetObjectName(customer.FirstName.Name, false, true, customer.DBType)
38	+ expression +
39	Utilities.GetObjectName(customer.LastName.Name, false, true, customer.DBType)
40	+ ")";
41
42	// Note: custom/calculated fields do not perform automatic case conversion so if you want your custom
43	// column to perform the case conversion you can use any of the alternate lines of code below (commented out).
44	//string expression = "UPPER(" + customer.FirstName.Name + " + ' ' + " + customer.LastName.Name + ")";
45	//string expression = "LOWER(" + customer.FirstName.Name + " + ' ' + " + customer.LastName.Name + ")";
46
47	// In version 3.x a new mode called GroupBy has been introduced in the ObjectModes enumeration.
48	// By setting the ObjectMode property of the customer object we are letting the customer object know
49	// that we want the customer object to created a query based on GROUP BY clause.
50	customer.ObjectMode = ObjectModes.GroupBy;
51
52	// Any objects that are joined to the customer object after the ObjectMode has been set will automatically receive the ObjectMode property value.
53	// This will mean that the Orders object's ObjectMode property will change to ObjectModes.GroupBy automatically.
54	customer.Join_CustomerID_Orders_Child();
55
56	// Here we create an instance of a StringField
57	// Each Field's constructor has multiple overloads, and we will use the one that accepts three parameters.
58	// First parameter is the business object that will contain the field, the second one is the Name of the field.
59	// Since we are going to create a custom field we use the Name property of the field as our expression. The last argument
60	// is used to specify the alias of the column.
61	// For a custom column to work correctly, you must specify a unique value for the ResultSetName property. In our case we specified
62	// this value in the constructor.
63	StringField sf = new StringField(customer, expression, "Name");
64
65	// IsCustom property must be set to true.
66	sf.IsCustom = true;
67
68	// Since we want to return order totals for each customer, we must group by the result set by the Name column.
69	// To achieve this, simply set the GroupType property to GroupTypes.GroupBy (introduced in v 3.x)
70	sf.GroupType = GroupTypes.GroupBy;
71
72	// We must specify the position of this field in the group by clause. Valid position is 1 or greater.
73	// To ensure accurate results, please ensure that position values are unique in all the fields included.
74	// GroupPosition value is only required if the GroupType value is set to GroupTypes.GroupBy
75	sf.GroupPosition = 1;
76
77	// To have the result set include order totals for a custom now we need to set the GroupType property of the OrderAmount
78	// field to GroupTypes.Sum
79	customer.CustomerID_Orders_Child.OrderAmount.GroupType = GroupTypes.Sum;
80
81	// Define the alias that will be used as the column name in the result set.
82	customer.CustomerID_Orders_Child.OrderAmount.ResultSetName.Value = "Order Amount";
83
84	// Since the OrderAmount field's GroupType property is set to GroupTypes.Sum
85	// it is not required that you set the GroupPosition value.
86	//customer.CustomerID_Orders_Child.OrderAmount.GroupPosition = 2;
87
88	customer.Find();
89
90	// if the Find method returned any records then we will set the Grid View's DataSource and DataMember properties accordingly.
91	if (customer.AffectedRecords > 0)
92	{
93	this.GridView1.DataSource = customer.ResultSet;
94	this.GridView1.DataMember = customer.GetResultSetName();
95	}
96	else
97	{
98	this.GridView1.DataSource = null;
99	this.GridView1.DataMember = null;
100	}
101	}
102

1	'Please note: This tutorial requires that the QO_SHOP sample database included with v 3.x.
2	'If you have have the the database schema from a previous version of QO_SHOP please upgrade the
3	'QO_SHOP database by using the Drop Objects.sql and Create Objects.sql. If you like the default
4	'test data to be automatically inserted, please use the Insert Data.sql file.
5
6	'The purpose of this tutorial is to display the ability to run Group By based SQL Statements without having to
7	'hard code any element of the statement. Use the fully strongly typed object model, and the Quick Objects will
8	'take care of the rest.
9
10	'In this tutorial we will return a list of Customer names along with their total order amount.
11	Using customer As Customers = New Customers()
12	' We are going to add a calculated field that will concatenate FirstName and LastName values to create a Name column.
13	' Lets check what database type is being used and assign the appropriate expression.
14	' If you know your database type for certain then you don't need to perform this check and you can directly
15	' assign the expression. Since this sample can be used with Oracle as well we are specifying
16	' both expressions to ensure compatibility.
17	Dim expression As String = String.Empty
18	If (customer.DBType = Akal.QuickObjects.DBLibrary.DatabaseObject.DatabaseTypes.SqlServer2000 Or customer.DBType = Akal.QuickObjects.DBLibrary.DatabaseObject.DatabaseTypes.SqlServer2005) Then
19	' in SQL Server a + sign is used to concatenate two string values.
20	expression = " + ' ' + "
21	Else
22	' in Oracle a \|\| symbols are used to concatenate two string values.
23	expression = "\|\| ' ' \|\|"
24	End If
25	' We can use the GetObjectName method of the Utilities to get a database representation of the FirstName field.
26	' This will ensure that we have not hard coded the underlying name and keeping everything strongly typed.
27	' Also, this method call will enclose the object name as necessary based on the database type.
28
29	expression = "(" & Utilities.GetObjectName(customer.FirstName.Name, False, True, customer.DBType) & expression & Utilities.GetObjectName(customer.LastName.Name, False, True, customer.DBType) & ")"
30
31	'Note: custom/calculated fields do not perform automatic case conversion so if you want your custom
32	'column to perform the case conversion you can use any of the alternate lines of code below (commented out).
33	'string expression = "UPPER(" + customer.FirstName.Name + " + ' ' + " + customer.LastName.Name + ")"
34	'string expression = "LOWER(" + customer.FirstName.Name + " + ' ' + " + customer.LastName.Name + ")"
35
36	'In version 3.x a new mode called GroupBy has been introduced in the ObjectModes enumeration.
37	'By setting the ObjectMode property of the customer object we are letting the customer object know
38	'that we want the customer object to created a query based on GROUP BY clause.
39	customer.ObjectMode = ObjectModes.GroupBy
40
41
42	'Any objects that are joined to the customer object after the ObjectMode has been set will automatically receive the ObjectMode property value.
43	'This will mean that the Orders object's ObjectMode property will change to ObjectModes.Grouping automatically.
44	customer.Join_CustomerID_Orders_Child()
45
46	'Here we create an instance of a StringField
47	'Each Field's constructor has multiple overloads, and we will use the one that accepts three parameters.
48	'First parameter is the business object that will contain the field, the second one is the Name of the field.
49	'Since we are going to create a custom field we use the Name property of the field as our expression. The last argument
50	'is used to specify the alias of the column.
51	'For a custom column to work correctly, you must specify a unique value for the ResultSetName property. In our case we specified
52	'this value in the constructor.
53	Dim sf As StringField = New StringField(customer, expression, "Name")
54
55	'IsCustom property must be set to true.
56	sf.IsCustom = True
57
58	'Since we want to return order totals for each customer, we must group by the result set by the Name column.
59	'To achieve this, simply set the GroupType property to GroupTypes.GroupBy (introduced in v 3.x)
60	sf.GroupType = GroupTypes.GroupBy
61
62
63	'We must specify the position of this field in the group by clause. Valid position is 1 or greater.
64	'To ensure accurate results, please ensure that position values are unique in all the fields included.
65	'GroupPosition value is only required if the GroupType value is set to GroupTypes.GroupBy
66	sf.GroupPosition = 1
67
68	'To have the result set include order totals for a custom now we need to set the GroupType property of the OrderAmount
69	'field to GroupTypes.Sum
70	customer.CustomerID_Orders_Child.OrderAmount.GroupType = GroupTypes.Sum
71
72	' Define the alias that will be used as the column name in the result set.
73	customer.CustomerID_Orders_Child.OrderAmount.ResultSetName.Value = "Order Amount"
74	'Since the OrderAmount field's GroupType property is set to GroupTypes.Sum
75	'it is not required that you set the GroupPosition value.
76	'customer.CustomerID_Orders_Child.OrderAmount.GroupPosition = 2
77
78	customer.Find()
79
80	'if the Find method returned any records then we will set the Grid View's DataSource and DataMember properties accordingly.
81
82	If customer.AffectedRecords > 0 Then
83	Me.GridView1.DataSource = customer.ResultSet
84	Me.GridView1.DataMember = customer.GetResultSetName()
85	Else
86	Me.GridView1.DataSource = Nothing
87	Me.GridView1.DataMember = Nothing
88	End If
89	End Using
90