MYSQL Combine results of multiple subqueries

Question

I need to combine the results of the following queries. I need to have the total messages processed internal and external together. Let me explain the queries and the data for them

Sample Data:

+---------------------+---------------------+----------------+---------------+----------------+
| deviceCustomString2 | destinationUserName | deviceHostName | deviceProduct | sourceUserName |
+---------------------+---------------------+----------------+---------------+----------------+
| <FD54>              | [email protected]     | gemslab385mb   | Test Server   | Exchange       |
| <FX54>              | [email protected]     | gemslabcht     | Test Server   | Exchange       |
| <FZ54>              | [email protected]     | gemslab385mb   | Test Server   | External       |
| <FA54>              | [email protected]     | gemslab385mb   | Test Server   | Exchange       |
| <FD54>              | [email protected]     | gemslab385mb   | Test Server   | Exchange       |
+---------------------+---------------------+----------------+---------------+----------------+

Query 1:

SELECT 
LEFT((LOWER(SUBSTRING_INDEX(b.deviceHostName,'.',1))),(LENGTH(SUBSTRING_INDEX(b.deviceHostName,'.',1))-2)) as "Device_Group",
COUNT(b.deviceCustomString2) as "Total_Messages_Processed_EXTERNAL"
FROM 
      ( SELECT                    
                   TEST2.deviceCustomString2,
                   TEST2.deviceHostName
         FROM
                   TEST2
         WHERE
                   TEST2.deviceProduct="Test Server"
         AND 
                   (TEST2.destinationUserName NOT LIKE '%dmroot.net%' 
                AND TEST2.destinationUserName NOT LIKE '%banco2%'
                AND TEST2.sourceUserName NOT LIKE '%Exchange%')
         GROUP BY
                   TEST2.deviceCustomString2,
                    TEST2.deviceHostName
                  ) as b

Query 1 Result:

+--------------+-----------------------------------+
| Device_Group | Total_Messages_Processed_EXTERNAL |
+--------------+-----------------------------------+
| gemslab385   |                                 1 |
+--------------+-----------------------------------+

Query2:

SELECT 
LEFT((LOWER(SUBSTRING_INDEX(a.deviceHostName,'.',1))),(LENGTH(SUBSTRING_INDEX(a.deviceHostName,'.',1))-2)) as "Device_Group",
    COUNT(a.deviceCustomString2) as "Total_Messages_Processed_INTERNAL"
FROM 
      ( SELECT                    
                   TEST2.deviceCustomString2,
                   TEST2.deviceHostName
         FROM
                   TEST2
         WHERE
                   TEST2.deviceProduct="Test Server"
     AND 
                   (
                    TEST2.destinationUserName LIKE '%dmroot.net%' 
                OR TEST2.sourceUserName  LIKE '%Exchange%'
               )
         GROUP BY
                   TEST2.deviceCustomString2


                  ) as a

GROUP BY
Device_group

Query 2 Result:

+--------------+-----------------------------------+
| Device_Group | Total_Messages_Processed_INTERNAL |
+--------------+-----------------------------------+
| gemslab385   |                                 2 |
| gemslabc     |                                 1 |
+--------------+-----------------------------------+

Results from Both queries are fine and it removes the duplicated record.

Now when I add UNION ALL between the 2 queries I get this result

+--------------+-----------------------------------+
| Device_Group | Total_Messages_Processed_EXTERNAL |
+--------------+-----------------------------------+
| gemslab385   |                                 1 |
| gemslab385   |                                 2 |
| gemslabc     |                                 1 |
+--------------+-----------------------------------+

The totals are correct, but not displaying the TOTAL_MESSAGES_PROCESSED_INTERNAL. how do I make it output something like this:

+--------------+-----------------------------------+-----------------------------------+
| Device_Group | Total_Messages_Processed_INTERNAL | Total_Messages_Processed_EXTERNAL | 
+--------------+-----------------------------------+-----------------------------------+
| gemslab385   |                                 2 |                                 1 |
| gemslabc     |                                 1 |                                   | 
+--------------+-----------------------------------+-----------------------------------+

Thank you in advance for your help.

Marcello

Answer 1

Prepare the list in the inner select, count groups conditionally in the outer select.

SELECT

  LEFT(LOWER(t.deviceGroup), LENGTH(t.deviceGroup) - 2) AS "Device_Group",

  COUNT(DISTINCT
    CASE
      WHEN t.destinationUserName NOT LIKE '%dmroot.net%' AND
           t.destinationUserName NOT LIKE '%banco2%' AND
           t.sourceUserName NOT LIKE '%Exchange%'
      THEN CONCAT(t.deviceCustomString2, '.', t.deviceHostName)
    END) AS "Total_Messages_Processed_EXTERNAL",

  COUNT(DISTINCT
    CASE
      WHEN t.destinationUserName LIKE '%dmroot.net%' OR
           t.sourceUserName LIKE '%Exchange%'
      THEN CONCAT(t.deviceCustomString2, '.', t.deviceHostName)
    END) AS "Total_Messages_Processed_INTERNAL"

FROM (
  SELECT
    deviceCustomString2,
    deviceHostName,
    SUBSTRING_INDEX(a.deviceHostName, '.', 1) AS deviceGroup,
    destinationUserName,
    sourceUserName
  FROM TEST2
  WHERE deviceProduct="Test Server"
) AS t

GROUP BY Device_Group

When counting groups, I'm using this construct: CONCAT(t.deviceCustomString2, '.', t.deviceHostName). You know your data better, and so it may appear that the expression can be changed simply to t.deviceCustomString2 or t.deviceHostName, only mind the DISTINCT keyword.

Additionally, to help the calculations slightly, I moved SUBSTRING_INDEX(a.deviceHostName, '.', 1) to the inner SELECT, so it doesn't evaluate twice.

Answer 2

What you want isn't really a union, it's just a normal join. Here's an example but I haven't tested the syntax so it might need some tweaks before it works:

(SELECT 
LEFT((LOWER(SUBSTRING_INDEX(b.deviceHostName,'.',1))),(LENGTH(SUBSTRING_INDEX(b.deviceHostName,'.',1))-2)) as "Device_Group",
COUNT(b.deviceCustomString2) as "Total_Messages_Processed_EXTERNAL", COUNT(a.deviceCustomString2) as "Total_Messages_Processed_INTERNAL"
FROM 
      ( SELECT                    
                   TEST2.deviceCustomString2,
                   TEST2.deviceHostName
         FROM
                   TEST2
         WHERE
                   TEST2.deviceProduct="Test Server"
         AND 
                   (TEST2.destinationUserName NOT LIKE '%dmroot.net%' 
                AND TEST2.destinationUserName NOT LIKE '%banco2%'
                AND TEST2.sourceUserName NOT LIKE '%Exchange%')
         GROUP BY
                   TEST2.deviceCustomString2,
                    TEST2.deviceHostName
                  ) as b LEFT JOIN 
      ( SELECT                    
                   TEST2.deviceCustomString2,
                   TEST2.deviceHostName
         FROM
                   TEST2
         WHERE
                   TEST2.deviceProduct="Test Server"
     AND 
                   (
                    TEST2.destinationUserName LIKE '%dmroot.net%' 
                OR TEST2.sourceUserName  LIKE '%Exchange%'
               )
         GROUP BY
                   TEST2.deviceCustomString2


                  ) as a ON a.deviceCustomString2 = b.deviceCustomString2 AND a.deviceHostName = b.deviceHostName

GROUP BY
Device_group