Skip to main content

Concat columns on GROUP BY in MySQL by Using GROUP_CONCAT

I was asked to give a report on the Invitations table as follows. What I wanted to give as the result is the invitee details per inviter. So simply I do not want to repeat the "inviter".

Invitations
invitee_name inviter invitee
gayan          345         4654
kosala          345         7655
chamara          567         7653
nilanka          567         1236

So basic query for the demanding result is

"SELECT * FROM Invitations GROUP BY inviter;"

 But then what happens is some results are omitted. So I need to concat the "invitee" and "invitee_name" columns when grouping by 'inviter'.

For this MySQL simply gives a function as “GROUP_CONCAT”. Details can be seen on
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat.

So for this example what I have to do is

SELECT GROUP_CONCAT(invitee_name SEPARATOR ' || ') AS inviteeName, inviter, GROUP_CONCAT(invitee SEPARATOR ' || ') AS inviteeID FROM Invitations GROUP BY inviter;

The result will be as follows.
inviteeName        inviter     inviteeID
gayan || kosala         345             4654 || 7655
chamara || nilanka 567             7653 || 1236

Currently as I can find this feature is available only on MySQL. This feature saved a lot of my time though it may reduce the performance. There are lot of options in "GROUP_CONCAT" and I have just used the separator since this was a CSV report. It separates by "," as default.

Comments

Post a Comment

Popular posts from this blog

Google API v3 with PHP using Blogger service

It was really hard for me to understand how the Google APIs are working at the first point and took few days for me to figure out. But after a successful working prototype it seems very easy. And also when I am searching for a simple example I was unable to find a good one that I can understand. So let me list down step by step what I have done with URLs and as simple as I can. Create a Google app location -  https://code.google.com/apis/console Switch on the "Blogger API v3" Get the latest APIs client library for PHP location -  https://code.google.com/p/google-api-php-client/downloads/list Upload the files to your host location on on localhost Extract the files to folder  named "GoogleClientApi" Create your php file outside of the folder  Copy paste following code into the file and do the changes as needed  By changing the scope and the service object you can access all the services that is given by Google APIs through the PHP API l...
122 comments
Read more

Assets and Liabilities as Rich Dad, Poor Dad explains

I was reading "The rich dad poor dad by Robert Kiyosaki" here is a one point that he mentions on that. Basically Asset as he says is little bit different than on books. If something puts money in your pocket it is a asset. And Liabilities are the ones that takes money out of your pocket. OK for example a house or a car may seems like an Asset but it takes money out of you pocket to maintain them. But if you rent them or make them to make money at the end of the day you can convert it to a asset. Basically that what rich people do. They buy assets. Middle class buy liabilities (thinking those are assets) and stuff (a lot of them that not used or that not needed). Lower class buy to consume (basic needs like foods).
44 comments
Read more

Simple book keeping basics

There are tons of jargon on accounting. But a lot of them are rubbish that used for categorizing the incomes, expenses and Tax. What really matters are Balance Sheet and P&L (profit & loss) statement. If you really knows to read and understand these two then you know the basic language in business.  I'll go deeper on these if you guys interest or else please Google and read about these two. Let me share a real world example of what I am talking about. Here is the Access Engineering PLC / Annual Report 2015/16 http://www.accessengsl.com/wp-content/uploads/2016/07/Annual-Report-2015-16.pdf Please go to page 149 of the report and you will see the Statement of Profit or Loss. And on page 150 of the report you see the Balance sheet (they call it as Statement of Financial Position). And you can see it is balanced (of cause it is balance sheet) Total Assets = Total Equity and Liabilities Basically these are the things that people are supposed look at before invest...
20 comments
Read more