I'm trying to figure out how many times in my database a particular grouping has occurred. I have two relevant tables.
mysql> describe logins;
+-------------+-------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(10) | NO | MUL | | |
| login_time | datetime | NO | MUL | 0000-00-00 00:00:00 | |
| logout_time | datetime | YES | | NULL | |
| duration | int(11) | YES | | NULL | |
| location | varchar(15) | YES | MUL | NULL | |
+-------------+-------------+------+-----+---------------------+----------------+
and
mysql> describe login_apps;
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| sid | int(11) | YES | | NULL | |
| programName | varchar(63) | YES | | NULL | |
| duration | int(11) | YES | | NULL | |
+-------------+-------------+------+-----+---------+----------------+
Lets say I want to know what applications were used in a given session. I can use:
mysql> select distinct programName from login_apps where sid="35" and programName!="NULL";
+--------------------+
| programName |
+--------------------+
| Acrobat Distiller |
| FireFox |
| Minitab 16 |
+--------------------+
to get the distinct program names, but I'm interesting in how often particular groups of applications are used in a given session. So essentially, how often are these three applications grouped together. Is there a way that can get me results in the form of
application x-y-z, 12
application x-z, 9
application y-z, 7
Best Answer
It feels like cheating to use
GROUP_CONCAT()
function: