This article explains various scenarios where GROUP_CONCAT group function should be used.
Scenario: 1
Sometimes, we need some requirements like concatenating two or more rows with a delimiter.
Example:
Following table shows the data of all the customers of a Life Insurance Company, who paid the premiums of their selected policies.
Table_Name: Customer_Payments
If your requirement is to find total amounts paid by the Customers along with list of policies holding by each & every customer. Here, the list of Policies holding by a Customer is showed with a delimiter comma.
Firstly, we need to group the Customer using Customer_Id
So,
SELECT Customer_Id, SUM(Payments) AS "Total Payment"
FROM Customer_Payments
GROUP BY Customer_Id;
Query Result:
The above query fetches the consolidated payments done by each Customer.
Now, you use GROUP_CONCAT(Policy_Type) in the SELECT clause to get the comma-separted list of Policies.
So,
SELECT Customer_Id, SUM(Payments) AS "Total Payment", GROUP_CONCAT(Policy_Type) AS "List Of Polices"
FROM Customer_Payments
GROUP BY Customer_Id;
Query Result:
Here, this means, Customer with Id 1, has 2 policies (01, 02) paid a total of 4000, Customer 2 has one policy (02) paid an amount 10000, & Customer 3 has one policy (01) paid an amount of 2500.
Note: By default, comma is the delimiter for GROUP_CONCAT group function.
You can choose your own delimiter for concatenation. This can be done using a SEPARATOR clause inside the GROUP_CONCAT group function.
Example:
SELECT Customer_Id, SUM(Payments),
GROUP_CONCAT(Policy_Type SEPARATOR '^^^') AS "List Of Policies"
FROM Customer_Payments
GROUP BY Customer_Id;
Query Result:
The SEPARATOR should be positioned always last inside the GROUP_CONCAT. Otherwise, you will end up with errors.
Example:
SELECT Customer_Id, SUM(Payments),
GROUP_CONCAT(Policy_Type ORDER BY Policy_Type DESC SEPARATOR '^^^')
FROM Customer_Payments
GROUP BY Customer_Id;
Query Result:
ORDERING WITH GROUP_CONCAT:
Sometimes, we also want list of policies separated by comma-separated in a specfic order. To do that, you have to use ORDER BY clause inside the GROUP_CONCAT group function.
Example:
SELECT Customer_Id, SUM(Payments) AS "Total Payment",
GROUP_CONCAT(Policy_Type ORDER BY Policy_Type DESC) AS "List Of Polices"
FROM Customer_Payments
GROUP BY Customer_Id;
Now, observe the Query Results:
For the first customer, having more than one policy, the policy numbers are now listed in DESCENDING order (02,01).
Scenario: 2
Lets consider that, we want the consolidated payments sorted by most recent payment: this can be done by sorting the data using Payment_Date
Example:
Table_Name: Customer_Payments
Please observe how we can build this Query. As we want the consolidated & most recent payers. So, we need to GROUP BY Customer.
So,
SELECT Customer_Id, SUM(Payments) AS "Total Payment", GROUP_CONCAT(Policy_Type) AS "List Of Policies"
FROM Customer_Payments
GROUP BY Customer_Id;
You need to add sorting logic to the query using the Column "Payment_date", but, as "Payment_date" is not in the GROUP BY clause, you can order by using the GROUP_CONCAT function in ORDER BY clause also.
So, before using GROUP_CONCAT with Payment_date column in ORDER_BY clause, lets see, how it fetches the data in SELECT clause.
SELECT Customer_Id, SUM(Payments) AS "Total Payment", GROUP_CONCAT(Policy_Type) AS "List Of Policies",
GROUP_CONCAT(Payment_date) AS "Payment Date"
FROM Customer_Payments
GROUP BY Customer_Id;
Query Results:
Now, you use GROUP_CONCAT in ORDER BY Clause:
SELECT Customer_Id, SUM(Payments) AS "Total Payment", GROUP_CONCAT(Policy_Type) AS "List Of Policies",
GROUP_CONCAT(Payment_date) AS "Payment Date"
FROM Customer_Payments
GROUP BY Customer_Id
ORDER BY GROUP_CONCAT(Payment_date ORDER BY Payment_date DESC);
Here, Overall Sorting Order: ASCENDING
Here, the consolidated data is sorted in ASCENDING order (Overall Query Sorting is in ASCENDING ORDER), Please observe the first Payment Date "2018-02-01 03:27:00". Whereas, In the group concatenated Payment Date, for instance, last row in result set, 2018-02-01 03:26:30,2018-02-01 03:30:33, out of these two dates, 2018-02-01 03:30:33 is considered, that is because GROUP concatenation done by Payment_dates & sorted in DESCENDING order, while the overall query sorting is in ASCENDING order.
Important Note:
There is much difference in these statements:
ORDER BY GROUP_CONCAT(Payment_date ORDER BY Payment_date DESC) - In the comma-separted list of payment dates 2018-02-01 03:30:33 is considered in the overall sorting.
ORDER BY GROUP_CONCAT(Payment_date ORDER BY Payment_date) - In the comma-separted list of payment dates 2018-02-01 03:26:30 is considered in the overall sorting.
So,
SELECT Customer_Id, SUM(Payments) AS "Total Payment", GROUP_CONCAT(Policy_Type) AS "List Of Policies",
GROUP_CONCAT(Payment_date) AS "Payment Date"
FROM Customer_Payments
GROUP BY Customer_Id
ORDER BY GROUP_CONCAT(Payment_date ORDER BY Payment_date);
Overall Sorting Order: ASCENDING
Observe the Query Results(especially the Payment Date)
Here, the consolidated data is sorted in ASCENDING order (Overall Query Sorting is in ASCENDING ORDER). Whereas, In the group concatenated Payment Date, for instance, first row in result set, 2018-02-01 03:26:30,2018-02-01 03:30:33, out of these two dates, 2018-02-01 03:26:30 is considered, that is because GROUP concatenation done by Payment_dates & sorted in ASCENDING order, & the overall query sorting is also in ASCENDING order.
SELECT Customer_Id, SUM(Payments) AS "Total Payment", GROUP_CONCAT(Policy_Type) AS "List Of Policies",
GROUP_CONCAT(Payment_date) AS "Payment Date"
FROM Customer_Payments
GROUP BY Customer_Id
ORDER BY GROUP_CONCAT(Payment_date ORDER BY Payment_date DESC) DESC;
Overall Sorting Order: DESCENDING
Query Results:
Here, the consolidated data is sorted in DESCENDING order (Overall Query Sorting is in DESCENDING ORDER). Whereas, In the group concatenated Payment Date, for instance, first row in result set, 2018-02-01 03:26:30,2018-02-01 03:30:33, out of these two dates, 2018-02-01 03:30:33 is considered, that is because GROUP concatenation done by Payment_dates & sorted in DESCENDING order, & the overall query sorting is also in DESCENDING order.
Now, change the Sorting Order in GROUP_CONCAT of Payment_date to ASCENDING without changing the Overall sorting order from DESCENDING.
SELECT Customer_Id, SUM(Payments) AS "Total Payment", GROUP_CONCAT(Policy_Type) AS "List Of Policies",
GROUP_CONCAT(Payment_date) AS "Payment Date"
FROM Customer_Payments
GROUP BY Customer_Id
ORDER BY GROUP_CONCAT(Payment_date ORDER BY Payment_date) DESC;
Overall Sorting Order: DESCENDING
Query Results:
Scenario: 1
Sometimes, we need some requirements like concatenating two or more rows with a delimiter.
Example:
Following table shows the data of all the customers of a Life Insurance Company, who paid the premiums of their selected policies.
Table_Name: Customer_Payments
If your requirement is to find total amounts paid by the Customers along with list of policies holding by each & every customer. Here, the list of Policies holding by a Customer is showed with a delimiter comma.
Firstly, we need to group the Customer using Customer_Id
So,
SELECT Customer_Id, SUM(Payments) AS "Total Payment"
FROM Customer_Payments
GROUP BY Customer_Id;
Query Result:
The above query fetches the consolidated payments done by each Customer.
Now, you use GROUP_CONCAT(Policy_Type) in the SELECT clause to get the comma-separted list of Policies.
So,
SELECT Customer_Id, SUM(Payments) AS "Total Payment", GROUP_CONCAT(Policy_Type) AS "List Of Polices"
FROM Customer_Payments
GROUP BY Customer_Id;
Query Result:
Here, this means, Customer with Id 1, has 2 policies (01, 02) paid a total of 4000, Customer 2 has one policy (02) paid an amount 10000, & Customer 3 has one policy (01) paid an amount of 2500.
Note: By default, comma is the delimiter for GROUP_CONCAT group function.
You can choose your own delimiter for concatenation. This can be done using a SEPARATOR clause inside the GROUP_CONCAT group function.
Example:
SELECT Customer_Id, SUM(Payments),
GROUP_CONCAT(Policy_Type SEPARATOR '^^^') AS "List Of Policies"
FROM Customer_Payments
GROUP BY Customer_Id;
Query Result:
The SEPARATOR should be positioned always last inside the GROUP_CONCAT. Otherwise, you will end up with errors.
Example:
SELECT Customer_Id, SUM(Payments),
GROUP_CONCAT(Policy_Type ORDER BY Policy_Type DESC SEPARATOR '^^^')
FROM Customer_Payments
GROUP BY Customer_Id;
Query Result:
ORDERING WITH GROUP_CONCAT:
Sometimes, we also want list of policies separated by comma-separated in a specfic order. To do that, you have to use ORDER BY clause inside the GROUP_CONCAT group function.
Example:
SELECT Customer_Id, SUM(Payments) AS "Total Payment",
GROUP_CONCAT(Policy_Type ORDER BY Policy_Type DESC) AS "List Of Polices"
FROM Customer_Payments
GROUP BY Customer_Id;
Now, observe the Query Results:
For the first customer, having more than one policy, the policy numbers are now listed in DESCENDING order (02,01).
Scenario: 2
Lets consider that, we want the consolidated payments sorted by most recent payment: this can be done by sorting the data using Payment_Date
Example:
Table_Name: Customer_Payments
Please observe how we can build this Query. As we want the consolidated & most recent payers. So, we need to GROUP BY Customer.
So,
SELECT Customer_Id, SUM(Payments) AS "Total Payment", GROUP_CONCAT(Policy_Type) AS "List Of Policies"
FROM Customer_Payments
GROUP BY Customer_Id;
You need to add sorting logic to the query using the Column "Payment_date", but, as "Payment_date" is not in the GROUP BY clause, you can order by using the GROUP_CONCAT function in ORDER BY clause also.
So, before using GROUP_CONCAT with Payment_date column in ORDER_BY clause, lets see, how it fetches the data in SELECT clause.
SELECT Customer_Id, SUM(Payments) AS "Total Payment", GROUP_CONCAT(Policy_Type) AS "List Of Policies",
GROUP_CONCAT(Payment_date) AS "Payment Date"
FROM Customer_Payments
GROUP BY Customer_Id;
Query Results:
Now, you use GROUP_CONCAT in ORDER BY Clause:
SELECT Customer_Id, SUM(Payments) AS "Total Payment", GROUP_CONCAT(Policy_Type) AS "List Of Policies",
GROUP_CONCAT(Payment_date) AS "Payment Date"
FROM Customer_Payments
GROUP BY Customer_Id
ORDER BY GROUP_CONCAT(Payment_date ORDER BY Payment_date DESC);
Here, Overall Sorting Order: ASCENDING
Here, the consolidated data is sorted in ASCENDING order (Overall Query Sorting is in ASCENDING ORDER), Please observe the first Payment Date "2018-02-01 03:27:00". Whereas, In the group concatenated Payment Date, for instance, last row in result set, 2018-02-01 03:26:30,2018-02-01 03:30:33, out of these two dates, 2018-02-01 03:30:33 is considered, that is because GROUP concatenation done by Payment_dates & sorted in DESCENDING order, while the overall query sorting is in ASCENDING order.
Important Note:
There is much difference in these statements:
ORDER BY GROUP_CONCAT(Payment_date ORDER BY Payment_date DESC) - In the comma-separted list of payment dates 2018-02-01 03:30:33 is considered in the overall sorting.
ORDER BY GROUP_CONCAT(Payment_date ORDER BY Payment_date) - In the comma-separted list of payment dates 2018-02-01 03:26:30 is considered in the overall sorting.
So,
SELECT Customer_Id, SUM(Payments) AS "Total Payment", GROUP_CONCAT(Policy_Type) AS "List Of Policies",
GROUP_CONCAT(Payment_date) AS "Payment Date"
FROM Customer_Payments
GROUP BY Customer_Id
ORDER BY GROUP_CONCAT(Payment_date ORDER BY Payment_date);
Overall Sorting Order: ASCENDING
Observe the Query Results(especially the Payment Date)
Here, the consolidated data is sorted in ASCENDING order (Overall Query Sorting is in ASCENDING ORDER). Whereas, In the group concatenated Payment Date, for instance, first row in result set, 2018-02-01 03:26:30,2018-02-01 03:30:33, out of these two dates, 2018-02-01 03:26:30 is considered, that is because GROUP concatenation done by Payment_dates & sorted in ASCENDING order, & the overall query sorting is also in ASCENDING order.
SELECT Customer_Id, SUM(Payments) AS "Total Payment", GROUP_CONCAT(Policy_Type) AS "List Of Policies",
GROUP_CONCAT(Payment_date) AS "Payment Date"
FROM Customer_Payments
GROUP BY Customer_Id
ORDER BY GROUP_CONCAT(Payment_date ORDER BY Payment_date DESC) DESC;
Overall Sorting Order: DESCENDING
Query Results:
Here, the consolidated data is sorted in DESCENDING order (Overall Query Sorting is in DESCENDING ORDER). Whereas, In the group concatenated Payment Date, for instance, first row in result set, 2018-02-01 03:26:30,2018-02-01 03:30:33, out of these two dates, 2018-02-01 03:30:33 is considered, that is because GROUP concatenation done by Payment_dates & sorted in DESCENDING order, & the overall query sorting is also in DESCENDING order.
Now, change the Sorting Order in GROUP_CONCAT of Payment_date to ASCENDING without changing the Overall sorting order from DESCENDING.
SELECT Customer_Id, SUM(Payments) AS "Total Payment", GROUP_CONCAT(Policy_Type) AS "List Of Policies",
GROUP_CONCAT(Payment_date) AS "Payment Date"
FROM Customer_Payments
GROUP BY Customer_Id
ORDER BY GROUP_CONCAT(Payment_date ORDER BY Payment_date) DESC;
Overall Sorting Order: DESCENDING
Query Results:
No comments:
Post a Comment