EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

PostgreSQL ARRAY_AGG()

By Sohel SayyadSohel Sayyad

Home » Data Science » Data Science Tutorials » PostgreSQL Tutorial » PostgreSQL ARRAY_AGG()

PostgreSQL ARRAY_AGG()

Introduction to PostgreSQL ARRAY_AGG()

The PostgreSQL provides various aggregate functions; the PostgreSQL ARRAY_AGG() aggregate function is used to get an array that will have each value of the input set added an element of the array. This aggregate function accepts a set of values as input, and the function includes NULL values into the array while concatenating the input values. We can use the ORDER BY clause with the PostgreSQL ARRAY_AGG() aggregate function in order to sort the result.

Syntax

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Consider the following syntax of the PostgreSQL ARRAY_AGG() aggregate function:

ARRAY_AGG(input_expression [ORDER BY [sort_expression {ASC | DESC}], [...])

  • ARRAY_AGG(): The PostgreSQL ARRAY_AGG() aggregate function takes the number of values as an input and then returns an array.
  • ORDER BY: This is an optional clause. This clause is used when we want the results sorted, which are processed in the aggregation, which results to sort the elements in the result array.

Examples to Implement PostgreSQL ARRAY_AGG()

We will create two tables of name ‘student’ and ‘department’ by using the CREATE TABLE statement as follows in order to understand the examples:

create table student
(
stud_id serial PRIMARY KEY,
stud_fname VARCHAR(80) NOT NULL,
stud_lname VARCHAR(80) NOT NULL,
department_id int NOT NULL
);
create table department
(
department_id serial PRIMARY KEY,
department_name VARCHAR(80) NOT NULL
);

Now, we will insert some data into the department table by using the INSERT INTO statement as follows.

INSERT INTO department(department_name)
VALUES
('Computer'),
('Electrical'),
('IT'),
('Civil'),
('Chemical'),
('Mechanical');

Illustrate the result of the above INSERT INTO statement by using the following SQL statement and snapshot.

Code:

select * from department;

Output:

CREATE TABLE

Now, we will insert some data into the student table by using the INSERT INTO statement as follows.

INSERT INTO student(stud_fname, stud_lname, department_id)
VALUES
('Smith','Johnson',1),
('Williams','Jones',1),
('Harper','James',2),
('Jack','Liam',2),
('Harry','Mason',3),
('Jacob','Oscar',3),
('Michael','Charlie',4),
('William','Joe',4),
('Oliver','John',5),
('Jack','Richard',5),
('Harry','Joseph',5),
('George','Thomas',6),
('Brown','Charles',6);

Illustrate the result of the above INSERT INTO statement by using the following SQL statement and snapshot.

Popular Course in this category
PostgreSQL Course (2 Courses, 1 Project)2 Online Courses | 1 Hands-on Project | 7+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.5 (3,283 ratings)
Course Price

View Course

Related Courses

Code:

select * from student;

Output:

PostgreSQL ARRAY_AGG() - 2

Example #1

Without ORDER BY clause in PostgreSQL ARRAY_AGG() aggregate function Consider the following SQL statement, which will use the function to return the list of names of the department and the list of names of the students studying in each department:

Code:

SELECT
department_name,
ARRAY_AGG (stud_fname || ' ' || stud_lname) students
FROM
department
INNER JOIN student USING (department_id)
GROUP BY
department_name
ORDER BY
Department_name;

Illustrate the result of the above SQL statement by using the following snapshot.

PostgreSQL ARRAY_AGG() - 3

From the above example, we can see that each department’s students are randomly ordered; to sort the students by their last name or first name, we have to define the ORDER BY clause in the PostgreSQL ARRAY_AGG() aggregate function.

Example #2

ORDER BY clause with PostgreSQL ARRAY_AGG() aggregate function

Consider the following example to get the list of students for each department, which are sorted by the student’s first name as shown in the following SQL statement:

Code:

SELECT
department_name,
ARRAY_AGG (
stud_fname || ' ' || stud_lname
ORDER BY
stud_fname
) students
FROM
department
INNER JOIN student USING (department_id)
GROUP BY
department_name
ORDER BY
department_name;

Illustrate the result of the above SQL statement by using the following snapshot.

PostgreSQL ARRAY_AGG() - 4

Example #3

ORDER BY clause with  PostgreSQL ARRAY_AGG() aggregate Function

Consider the following example to sort the list of students for each department by student’s first name and last name as shown in the following SQL statement:

Code:

SELECT
department_name,
ARRAY_AGG (
stud_fname || ' ' || stud_lname
ORDER BY
stud_fname ASC,
stud_lname DESC
) student
FROM
department
INNER JOIN student USING (department_id)
GROUP BY
department_name
ORDER BY
department_name;

Illustrate the result of the above SQL statement by using the following snapshot.

ORDER BY

Conclusion

We hope from the above article you have understood how to use the PostgreSQL ARRAY_AGG() aggregate function and how the aggregate function works. Also, we have added several examples of the aggregate functions to understand them in detail.

Recommended Articles

This is a guide to PostgreSQL ARRAY_AGG(). Here we discuss an introduction to PostgreSQL ARRAY_AGG() with syntax and examples. You can also go through our other related articles to learn more –

  1. PostgreSQL LAG()
  2. PostgreSQL CONCAT()
  3. PostgreSQL Interval
  4. PostgreSQL Math Functions

PostgreSQL Course (2 Courses, 1 Project)

2 Online Courses

1 Hands-on Project

7+ Hours

Verifiable Certificate of Completion

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
MongoDB Tutorial
  • Advanced
    • MongoDB Array
    • PostgreSQL ARRAY_AGG()
    • Indexes in MongoDB
    • MongoDB create Index
    • MongoDB Collection
    • MongoDB List Collections
    • MongoDB Capped Collections
    • MongoDB Auto Increment
    • MongoDB Projection
    • Replication in MongoDB
    • Mongo DB Create Database
    • MongoDB Compass
    • MongoDB Users
    • MongoDB Authentication
    • MongoDB GridFS
    • MongoDB Relationships
    • MongoDB Monitoring
    • Backup in MongoDB
    • MongoDB Sharding
    • MongoDB Java Drivers
    • MongoDB Import
    • Mongo Database Interview Questions
  • Basics
    • What is MongoDB
    • How To Install MongoDB
    • MongoDB Tools
    • MongoDB GUI Tools
    • MongoDB Versions
    • MongoDB Commands
    • Advantages of MongoDB
    • MongoDB Features
    • Is MongoDB NoSQL
    • Is MongoDB Open Source
    • Build Web Applications using MongoDB
    • MongoDB vs Elasticsearch
    • MariaDB vs MongoDB
    • Firebase vs MongoDB
  • Commands
    • MongoDB Limit()
    • MongoDB count
    • MongoDB Aggregate
    • MongoDB Distinct
    • MongoDB Unique
    • MongoDB find
    • MongoDB insert
    • MongoDB Delete
    • MongoDB Update
    • Lookup in MongoDB
    • order by in MongoDB
    • MongoDB $regex
    • MongoDB $elemMatch
    • MongoDB ObjectId()
    • MongoDB Skip()
    • MongoDB Date Query
    • MongoDB Timestamp
    • MongoDB sort()
    • MongoDB group by
    • MongoDB Join

Related Courses

MongoDB Certification Course

Oracle Certification Course

All in One Data Science Course

SQL Training Course

Oracle DBA Course

MS SQL Certification Course

Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

© 2020 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA Login

Forgot Password?

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you
Book Your One Instructor : One Learner Free Class

Let’s Get Started

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

Special Offer - PostgreSQL Course (2 Courses, 1 Project) Learn More