Search This Blog

Tuesday, 6 February 2018

HOW & WHEN "GROUP_CONCAT" GROUP FUNCTION IS USED ? - MYSQL

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

Payment_No CustomerCustomer_IdPaymentsPolicy_TypePayment_date
00001Ramesh11000012018-02-01 03:26:30
00002Rajesh210000022018-02-01 03:27:00
00003Ramesh13000022018-02-01 03:30:33
00004Geeta32500012018-02-01 03:28:31

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:

Customer_IdTotal Payment
14000
210000
32500


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:


Customer_Id Total PaymentList Of Policies
1400001,02
21000002
3250001

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:

Customer_IdTotal PaymentList Of Policies
1400001^^^02
21000002
3250001

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:


Customer_IdTotal PaymentList Of Policies
1400002^^^01
21000002
3250001

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:

Customer_IdTotal PaymentList Of Policies
1400002,01
21000002
3250001

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

Payment_No CustomerCustomer_IdPaymentsPolicy_TypePayment_date
00001Ramesh11000012018-02-01 03:26:30
00002Rajesh210000022018-02-01 03:27:00
00003Ramesh13000022018-02-01 03:30:33
00004Geeta32500012018-02-01 03:28:31

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:

Customer_IdTotal PaymentList Of PoliciesPayment Date
1400002,012018-02-01 03:26:30,2018-02-01 03:30:33
21000022018-02-01 03:27:00
3250012018-02-01 03:28:31
 
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

Customer_IdTotal PaymentList Of policiesPayment Date
210000022018-02-01 03:27:00
32500012018-02-01 03:28:31
1400002,012018-02-01 03:26:30,2018-02-01 03:30:33

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)

Customer_IdTotal PaymentList Of policiesPayment Date
1400002,012018-02-01 03:26:30,2018-02-01 03:30:33
210000022018-02-01 03:27:00
32500012018-02-01 03:28:31

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:

Customer_IdTotal PaymentList Of policiesPayment Date
1400002,012018-02-01 03:26:30,2018-02-01 03:30:33
32500012018-02-01 03:28:31
210000022018-02-01 03:27:00

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:

Customer_IdTotal PaymentList Of policiesPayment Date
3250012018-02-01 03:28:31
21000022018-02-01 03:27:00
1400002,012018-02-01 03:26:30,2018-02-01 03:30:33

Tuesday, 28 February 2017

HOW TO INSTALL PLUGINS IN INTELLIJ IDEA IDE?

This article shows the sequential screenshots to download & install the plugins in IntelliJ IDE.

Here, I am showing the JMeter plugin installation:

Step 1:  Go to preferences from menu bar as below: or

Press keyboard shortcut (Command + Comma)  ⌘, on Mac

File -> Settings on Windows & Linux



Step 2: Directly shows all the available plugins in the IDE.


Step 3: First search for the JMeter in the available plugins. You see this screen if its not already installed.


Step 4:  Click Browse repositories button, which loads all the repository plugins


Step 5: Now search for the plugin you would like to install, eg: JMeter. Then right click on the plugin & click "Download and install".


Step 6: Prompts the user whether to begin download & installation process


Step 7: If proceeded with Yes, it automatically downloads & install the plugin:


Step 8: After successful installation, IntelliJ asks for a restart: Just like below



 Step 9: Ensure the plugin downloaded & installed successfully by searching for jmeter plugin in the available plugins again.


                                                     HOPE IT IS HELPFUL ......PLEASE SHARE.........