A GROUP BY can be used to consolidate information from several rows into one row.
The following instructions will step through the process to recreate a view using a GROUP BY with the COUNT Function in the Query Builder.
To continue, launch R:BASE 11 and connect to the RRBYW20 sample database. The database is located in the following default installation directory: "C:\RBTI\RBG11\Samples\RRBYW20".
1.Start R:BASE
2.Connect to the RRBYW20 sample database, by selecting "Database" > "Connect", and navigating to the above default installation directory based upon your version.
3.Then, launch the Query Builder by selecting "Tools" > "Query by Example" from the main menu bar
In the Query Builder you will see a list of tables and views within a panel to the left that can be added to your query. The list contains all tables and views for the connected database.
4.Add the "Titles" table to the query by selecting the table, and then selecting the "Add Table/View" button.
The table should now be listed under "Tables/Views In Use".
5.Add the "Employee" table to the query by selecting the table, and then selecting the "Add Table/View" button.
The table should now be listed under "Tables/Views In Use". With the tables added, the specific column(s) to be used in the query can be selected.
6.Right click on the "Titles" table and select "Select Columns ..."
7.Add the "EmpTitle" column. When prompted for a column alias, just select the OK button.
8.Click on the "New Expression" button.
9.Using the "Table Name" drop down box, select "Employee" as the table name.
10. From the "Function Templates", select "(COUNT(arg))", then the "Use" button. The COUNT function will appear in the "Expression Text" panel.
11. Within the "Expression Text" panel, remove the text "arg", and select "EmpID" from the list of "Columns", then select the "Add Column" button.
12. Select the "OK" button.
The "Current Selection" results should look like this:
13. Select the "OK" button to return to the Query Builder.
14. Right click on the "Titles" table under "Tables/Views In Use", and choose "GROUP BY" from the speed menu.
15. Select the "EmpTitle" column, leave the "Ascending" radio button selected, and click the "Add" button.
16. Select the "OK" button to return to the Query Builder.
In the Query Builder main window, you should see the SQL syntax displayed in the bottom of the page as:
SELECT T1.EmpTitle,(COUNT( T2.EmpID ))
FROM Titles T1, Employee T2
WHERE T1.EmpTID = T2.EmpTID
GROUP BY T1.EmpTitle
17. Now, browse the query results by selecting "Query" > "Browse Query" from the main menu bar. The results should look like the following:
The results should display a column of employee titles, and the number of employees with each title. Close the Data Browser window.
A HAVING clause can be added to only display job titles with "sales" in the name.
18. Right click on the "Titles" table under "Tables/Views In Use", and choose "GROUP BY" from the speed menu.
19. Within the "HAVING Clause" panel, enter: EmpTitle CONTAINS 'Sales'
20. Select the "OK" button to return to the Query Builder.
In the Query Builder main window, you should see the SQL syntax displayed in the bottom of the page as:
SELECT T1.EmpTitle,(COUNT( T2.EmpID ))
FROM Titles T1, Employee T2
WHERE T1.EmpTID = T2.EmpTID
GROUP BY T1.EmpTitle
HAVING EmpTitle CONTAINS 'Sales'
21. Now, browse the query results by selecting "Query" > "Browse Query" from the main menu bar. The results should look like the following:
The results should display a column of employee titles that contain the word "sales" in the name, and the number of employees with each title. Close the Data Browser window.
A HAVING clause can also be used to only display limited results based upon employee titles that occur more than once.
22. Right click on the "Titles" table under "Tables/Views In Use", and choose "GROUP BY" from the speed menu.
23. Within the "HAVING Clause" panel, enter: COUNT (EmpTitle) > 1
24. Select the "OK" button to return to the Query Builder.
In the Query Builder main window, you should see the SQL syntax displayed in the bottom of the page as:
SELECT T1.EmpTitle,(COUNT( T2.EmpID ))
FROM Titles T1, Employee T2
WHERE T1.EmpTID = T2.EmpTID
GROUP BY T1.EmpTitle
HAVING COUNT (EmpTitle) > 1
25. Now, browse the query results by selecting "Query" > "Browse Query" from the main menu bar. The results should look like the following:
The results should display a column of employee titles that occur more than once. Close the Data Browser window.
If you wish to save the query, follow the below steps.
1.From the main menu bar, select "File" > "Save Query As View..."
2.In the dialog, enter "JobTitle" within the "View Name:" field.
3.In the dialog, enter "Employee Job Titles" within the "View Comment:" field.
4.Select the "OK" button.
5.Close the Query Builder by selecting "File" > "Close" from the menu bar.
In the Database Explorer, the view will be displayed with the name, comment, and number of columns.
