0

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
  • So for each login, which apps were used; and for each of these combinations, how many times? – Jon of All Trades Sep 6 '13 at 22:35
  • @JonofAllTrades: The end result is how many of each combination occurred. I don't care about the first part. – Jacobm001 Sep 6 '13 at 22:36
  • Is login_apps.sid an FK to logins.id? – Jon of All Trades Sep 6 '13 at 22:36
  • @JonofAllTrades: Yes, but it's a MyISAM engine, so while FK's aren't technically supported, that's what it references. – Jacobm001 Sep 6 '13 at 22:37
1

It feels like cheating to use GROUP_CONCAT() function:

SELECT
      apps
    , COUNT(*) AS combination_count
FROM
      ( SELECT
              GROUP_CONCAT(DISTINCT programName ORDER BY programName) AS apps
        FROM 
              login_apps
        GROUP BY 
              sid
      ) AS grp
GROUP BY
      apps ;
  • Why does it feel like cheating? It works well, and doesn't seem inefficient. – Jacobm001 Sep 6 '13 at 23:42
  • Because it's not a set-based solution. – ypercubeᵀᴹ Sep 6 '13 at 23:46
1

I can offer how I'd handle it in MS SQL, using PIVOT and ROW_NUMBER(). These don't appear to exist in MySQL (perhaps the latest version has added them?), so I'm afraid it won't be helpful to you. Perhaps someone more familiar with MySQL can use this to help get to a solution.

SELECT
    "1"
    + CASE WHEN "2" IS NULL THEN '' ELSE '-' + "2" END
    + CASE WHEN "3" IS NULL THEN '' ELSE '-' + "3" END AS Applications,
    Instances
FROM
    (
    SELECT
        SID, ProgramName, ROW_NUMBER() OVER (PARTITION BY SID ORDER BY ProgramName) AS Seq, Instances
    FROM
        (
        SELECT
            SID, ProgramName, COUNT(*) AS Instances
        FROM
            Login_Apps
        GROUP BY
            SID, ProgramName
        ) AS X
    ) AS Y
PIVOT
    (MIN(ProgramName) FOR Seq IN ("1", "2", "3")) AS Z

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.