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.
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.
Great content! Super high-quality! Keep it up!
ReplyDeletePython Tkinter Frame Widget
Python Tkinter Checkbutton Widget
Python Tkinter Combobox Event Binding
Python Tkinter Combobox
Python Tkinter Text Widget