How can we group multiple record by foreign key

Question

Suppose I have two table one is A and second is B. A has primary key Aid and B has foreign key B_foreignid which contain Aid i.e table A primary key and table B contain multiple Aid as foreign key.So my question is how can we group record fetch from table B by Aid that is A primary key

Answer 1

If I understood your question correctly, you want to group your records by foreign key?

Suppose there is a table Employee:

| EmployeeId | EmployeeName | Salary | DepartmentId
| 1          | John         |  500   | 1
| 2          | Robert       |  400   | 3
| 3          | John         |  350   | 2
| 4          | Robert       |  200   | 2
| 5          | John         |  600   | 1

And table Department:

| DepartmentId | DepartmentName
| 1            | IT
| 2            | Sales
| 3            | Accounting

So to group data from a table Employee by their department (foreign key) you should choose whether you need to have an aggregate function e.g find the number of employees for each department, the sum of their salaries, minimum and maximum salaries in each department and so on.

For getting total salaries for each department you can use the query:

SELECT SUM(Salary) AS 'Total Salary', DepartmentName AS 'Department'
FROM Employee e
INNER JOIN Department d ON e.DepartmentId d.DepartmentId
ORDER BY DepartmentName;

The result of the query for the given data above will be:

| Total Salary | Department
| 400          | Accounting
| 1100         | IT
| 550          | Sales

Most of the time when you group data by some criteria, you want to use aggregated functions with it, but not always.

When you want to use aggregate functions:

• https://www.w3schools.com/sql/sql_groupby.asp
• https://www.postgresql.org/docs/9.5/functions-aggregate.html

When you don't need them:

• GROUP BY without aggregate function

But if you just want to order your data by some criteria so that when you fetch them they are grouped, you can use 'ORDER BY', for the example above it would look like this:

SELECT EmployeeName AS 'Employee', DepartmentId AS 'Department'
FROM Employee
ORDER BY DepartmentId;

The result will be like this:

| Employee | Department
| John     | 1
| John     | 1
| John     | 2
| Robert   | 2
| Robert   | 3