Mysql – group_concat and group by together

group bygroup-concatenationMySQL

I am trying to write a query using group_concat, concat and other functions, but I am getting stuck not able to join and group the data to represent them in 1 cell.

Here's how my data is…

table1
+-----+---------+
| id  | ...     |
+-----+---------+
| 1   |         |
| 2   |         |
| 3   |         |
+-----+---------+
table2
+-----+-----+-------+
| id  | env | infid |
+-----+-----+-------+
| 1   | p   | 10    |
| 1   | p   | 11    |
| 1   | p   | 20    |
| 1   | p   | 12    |
| 1   | D   | 21    |
| 1   | D   | 22    |
+-----+-----+-------+
table3
+-------+---------+---------+
| infid | inftype | infname |
+-------+---------+---------+
| 10    | Srv     | abc     |
| 20    | Srv     | xyz     |
| 11    | Db      | hgj     |
| 12    | Db      | kjk     |
| 21    | Srv     | pop     |
| 22    | Db      | kli     |
+-------+---------+---------+

expected query result

+-----+------------------------------+---------------------+
| id  | P                            | D                   |
+------------------------------------+---------------------+
| 1   |Srv: [abc, xyz] Db: [hgj, kjk]|Srv: [pop] Db: [Kli] |
+-----+------------------------------+---------------------+

Any help is greatly appreciated.

Best Answer

The first thing you need is a messy query to create each column

FIRST PHASE OF QUERY

SELECT env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
ORDER BY inftype DESC SEPARATOR ' ') tags
FROM (SELECT env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
FROM table1 AAA
INNER JOIN table2 BBB ON AAA.id = BBB.id
INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
GROUP BY env,inftype) A GROUP BY env;

YOUR SAMPLE DATA

mysql> DROP DATABASE IF EXISTS kumar_concat;
Query OK, 3 rows affected (0.03 sec)

mysql> CREATE DATABASE kumar_concat;
Query OK, 1 row affected (0.00 sec)

mysql> USE kumar_concat
Database changed
mysql> CREATE TABLE table1
    -> (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO table1 VALUES (),(),();
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE table2
    -> (
    ->     id INT NOT NULL,
    ->     env VARCHAR(10) NOT NULL,
    ->     infid INT NOT NULL,
    ->     PRIMARY KEY (id,infid)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO table2 (id,env,infid) VALUES
    -> (1,'P',10), (1,'P',11), (1,'P',20),
    -> (1,'P',12), (1,'D',21), (1,'D',22);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE table3
    -> (
    ->     infid INT NOT NULL,
    ->     inftype VARCHAR(10) NOT NULL,
    ->     infname VARCHAR(10) NOT NULL,
    ->     PRIMARY KEY (infid)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO table3 (infid,inftype,infname) VALUES
    -> (10,'Srv','abc'), (20,'Srv','xyz'), (11,'Db','hgj'),
    -> (12,'Db','kjk'), (21,'Srv','pop'), (22,'Db','kli');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql>

FIRST PHASE OF QUERY EXECUTED

mysql> SELECT env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
    -> ORDER BY inftype DESC SEPARATOR ' ') tags
    -> FROM (SELECT env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
    -> FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
    -> FROM table1 AAA
    -> INNER JOIN table2 BBB ON AAA.id = BBB.id
    -> INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
    -> GROUP BY env,inftype) A GROUP BY env;
+-----+------------------------------+
| env | tags                         |
+-----+------------------------------+
| D   | Srv [pop] Db [kli]           |
| P   | Srv [abc, xyz] Db [hgj, kjk] |
+-----+------------------------------+
2 rows in set (0.02 sec)

mysql>

Let's create each for each env value

QUERY

SELECT
    T1.tags P,T2.tags D
FROM
(SELECT env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
ORDER BY inftype DESC SEPARATOR ' ') tags
FROM (SELECT env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
FROM table1 AAA
INNER JOIN table2 BBB ON AAA.id = BBB.id
INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
GROUP BY env,inftype) A GROUP BY env) T1,
(SELECT env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
ORDER BY inftype DESC SEPARATOR ' ') tags
FROM (SELECT env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
FROM table1 AAA
INNER JOIN table2 BBB ON AAA.id = BBB.id
INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
GROUP BY env,inftype) A GROUP BY env) T2
WHERE T1.env='P'
AND T2.env='D';

QUERY EXECUTED

mysql> SELECT
    ->     T1.tags P,T2.tags D
    -> FROM
    -> (SELECT env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
    -> ORDER BY inftype DESC SEPARATOR ' ') tags
    -> FROM (SELECT env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
    -> FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
    -> FROM table1 AAA
    -> INNER JOIN table2 BBB ON AAA.id = BBB.id
    -> INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
    -> GROUP BY env,inftype) A GROUP BY env) T1,
    -> (SELECT env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
    -> ORDER BY inftype DESC SEPARATOR ' ') tags
    -> FROM (SELECT env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
    -> FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
    -> FROM table1 AAA
    -> INNER JOIN table2 BBB ON AAA.id = BBB.id
    -> INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
    -> GROUP BY env,inftype) A GROUP BY env) T2
    -> WHERE T1.env='P'
    -> AND T2.env='D';
+------------------------------+--------------------+
| P                            | D                  |
+------------------------------+--------------------+
| Srv [abc, xyz] Db [hgj, kjk] | Srv [pop] Db [kli] |
+------------------------------+--------------------+
1 row in set (0.05 sec)

mysql>

FINAL QUERY (with id wedged in)

SELECT
    T1.id,T1.tags P,T2.tags D
FROM
(SELECT id,env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
ORDER BY inftype DESC SEPARATOR ' ') tags
FROM (SELECT id,env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
FROM table1 AAA
INNER JOIN table2 BBB ON AAA.id = BBB.id
INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
GROUP BY id,env,inftype) A GROUP BY id,env) T1
INNER JOIN
(SELECT id,env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
ORDER BY inftype DESC SEPARATOR ' ') tags
FROM (SELECT id,env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
FROM table1 AAA
INNER JOIN table2 BBB ON AAA.id = BBB.id
INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
GROUP BY id,env,inftype) A GROUP BY id,env) T2 
USING (id) WHERE T1.env='P' AND T2.env='D';

FINAL QUERY (with id wedged in) EXECUTED

mysql> SELECT
    ->     T1.id,T1.tags P,T2.tags D
    -> FROM
    -> (SELECT id,env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
    -> ORDER BY inftype DESC SEPARATOR ' ') tags
    -> FROM (SELECT id,env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
    -> FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
    -> FROM table1 AAA
    -> INNER JOIN table2 BBB ON AAA.id = BBB.id
    -> INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
    -> GROUP BY id,env,inftype) A GROUP BY id,env) T1
    -> INNER JOIN
    -> (SELECT id,env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
    -> ORDER BY inftype DESC SEPARATOR ' ') tags
    -> FROM (SELECT id,env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
    -> FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
    -> FROM table1 AAA
    -> INNER JOIN table2 BBB ON AAA.id = BBB.id
    -> INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
    -> GROUP BY id,env,inftype) A GROUP BY id,env) T2
    -> USING (id) WHERE T1.env='P' AND T2.env='D';
+----+------------------------------+--------------------+
| id | P                            | D                  |
+----+------------------------------+--------------------+
|  1 | Srv [abc, xyz] Db [hgj, kjk] | Srv [pop] Db [kli] |
+----+------------------------------+--------------------+
1 row in set (0.08 sec)

mysql>

GIVE IT A TRY !!!

Related Solutions

Mysql – Optimizing a query so MySQL will use the indexes for the joins

Here are my notices:

Make sure that creator exists only in table1, or add [... WHERE table1.creator=..]
It could be normal that the index on table2 is not used, specially if the majority or rows have the same value of the key.
From the profiling, sending data is the longest status. This means that the amount of data the query returns is huge (You mentioned 'long list of fields..'). Try to execute the query by selecting one field only, or be select count(*) and see if there is a significant change
Note that 400*270=108000, which is not a tiny number of scanned rows :)

Mysql – Nested MySQL FROM query with two DBs

There are basically two options with your scenario, as I understand it.

Dynamic SQL (see a_vlad's answer)
A (potentially) insanely massive JOIN.
Return "canned" data.

A massive JOIN

For option two, you have to know the names of your tables when you write the query - and, those tables have to exist.

So, step one would be determine any future tables that will be needed, and pre-create them. If you can't do this, you can still build the query - but it will have to be modified whenever a new table is added.

So, assuming that your criteria allow us to create as many tables as you'll need for the foreseeable future in advance, next you have to build the new query:

SELECT DATE(COALESCE(t1.timestamp,t2.timestamp, t3.timestamp, t4.timestamp)) As Date
      ,AVG(COALESCE(t1.Value,t2.Value,t3.Value,t4.Value)) AS Value
FROM
  cmsdb.usertable ut
  LEFT JOIN cmsdb.grouptable gt ON ut.groupID = gt.ID
  LEFT JOIN Table1 t1 ON (gt.tablename = 'Table1')
  LEFT JOIN Table2 t2 ON (gt.tablename = 'Table2')
  LEFT JOIN Table3 t3 ON (gt.tablename = 'Table3')
  LEFT JOIN Table4 t4 ON (gt.tablename = 'Table4')
WHERE u.User = 2
  AND COALESCE(t1.timestamp,t2.timestamp, t3.timestamp, t4.timestamp) BETWEEN CURDATE()-INTERVAL 4 WEEK AND CURDATE()
GROUP BY DATE(COALESCE(t1.timestamp,t2.timestamp, t3.timestamp, t4.timestamp))

As you can see, this is already pretty unwieldy with 4 tables - with 200, it'd be hideous.

NOTE: code is untested, and I don't mainly work w/ MySQL, but this is simple enough that it should work; you might need to play with exactly where the COALESCE function should be.

The key here, if it isn't obvious, is that we only actually pay attention to data from the table with the right name.

There are a couple of assumptions here:

Only one table can be valid for any given user. If a user can belong to multiple groups and thus see multiple tables, the results may not be correct.
This assumes that you really want a summary of all the table's contents in the date range for any user in the correct group; if there's anything user-specific, you'd need to add that check, using COALESCE again to pick up the column form the table we actually joined with.

In many ways, a dynamic SQL solution would be easier on the programmer. With that, you can avoid some of the possible problems with this (it can't handle new tables, renamed tables, etc.)

Canned data

The third option only works if the data returned does not have to be live.

For this option, you create another table - let's call it GroupSummary. This table has three key values: grouptable (can be used as key), SummaryDate, AvgValue

GroupSummary would be populated by a dynamic SQL statement, like in a_vlad's answer. You could loop through the possible tables with a cursor, and have the whole thing set up as a stored program. Run the stored program as often as needed to pick up new data (noting that running it too frequently is likely to cause performance issues with your SELECT query). If reasonably fresh data is required, I'd be thinking refreshing the data every 5-15 minutes; at a minimum, it should be refreshed after midnight (as this would, of course, change the base date it's working from).

Then, the query in your webpage would be:

SELECT SummaryDate as Date
      ,AvgValue as Value
  FROM GroupSummary gs
         INNER JOIN grouptable gt ON (gs.grouptable = gt.grouptable)
           INNER JOIN usertable ut ON (gt.ID = ut.groupID)
 WHERE User = %USERID%

(Note: WHERE clause based on your sample query, adjust as needed).