Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
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

2 Answers 2

up vote 1 down vote accepted

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 ;
share|improve this answer
    
That's wonderfully simple. Thank you. –  Jacobm001 Sep 6 '13 at 23:25
    
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

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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