Question 403666 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Get count of each email type PER table

Following up on from my previous question: Get count of each email type from multiple tables

I had to get clarification…

So I do indeed need to have multiple tables.

I then need to get a count of all the ‘gmails’, ‘msfts’, ‘others’, and ‘vmgs’ from each table.

All the tables have the same design. Table1 may resemble this:

| EMAIL | MY_ISP | STATUS |
 -------------------------
| email1| gmail  | active |
 -------------------------
| email2| msft   | unsub  |
 -------------------------
| email3| vmg    | active |
 -------------------------
| email4| gmail  | active |
 -------------------------

With table2 possibly looking like this:

| EMAIL | MY_ISP | STATUS |
 -------------------------
| email1| gmail  | unsub  |
 -------------------------
| email2| msft   | active |
 -------------------------
| email3| vmg    | active |
 -------------------------
| email5| gmail  | unsub  |
 -------------------------

An email may exist in multiple tables, but won’t be listed in the same table twice. The same email may be ACTIVE in one or more tables, and may not exist in another.

I need to get a running count of all emails per table that are ACTIVE.

And I need to display the end product this:

TABLE | GMAIL | MSFT | OTHER | VMG 
-----------------------------------
T1    | 5000  | 7000 | 4500  | 475
-----------------------------------
T2    | 4520  | 6789 | 4450  | 425
-----------------------------------
T3    | 4400  | 6500 | 4123  | 410
-----------------------------------

Something like that. All the way down to T15.

As I keep adding more tables, the number totals should gradually decrease until they are practically 0.

I hope that makes sense.

I am having trouble wrapping my mind around this logic.

Per my previous question, I can get the total of the MY_ISP using this query:

SELECT COUNT(`my_isp`) AS 'COUNT', `my_isp` FROM `table1` GROUP BY `my_isp`

And will spit out something like this:

COUNT | my_isp
---------------
4000  | Gmail
---------------
2000  | MSFT
---------------
10000 | other
---------------
15000 | VMG
---------------

And I can get the totals for each MY_ISP using something like this:

select my_isp ,sum(cnt) AS total
 from (select my_isp,count(t1.`my_isp`) cnt from `table1` t1 group by my_isp
      UNION ALL
       select my_isp,count(t2.`my_isp`) cnt from `table2` t2 group by my_isp
      ) subquery_name
 group by my_isp

The above actually works splendidly. But it’s not what was requested.

So just to reiterate, I need to get a count of GMAILS, MSFT, VMG, and OTHER per each table.

Please help me figure out how this query should look, because I am lost.

I will keep running tests and update question in the event I make some progress.

** EDIT **

I’m thinking maybe I need to create a VIEW that will display the different MY_ISP as the column headers, and the each TABLE as the rows.

Tags:

2

Answers


  1. 0

    Add the table name as another column in each query in the UNION

    select table_name, my_isp ,sum(cnt) AS total
from (
    select 't1' AS table_name, my_isp, count(t1.`my_isp`) cnt from `table1` t1 group by my_isp
    UNION ALL
    select 't2' AS table_name, my_isp, count(t2.`my_isp`) cnt from `table2` t2 group by my_isp
) subquery_name
group by table_name, my_isp

    To get each provider into a column you can pivot the resulting data. See How can I return pivot table output in MySQL?

    Login or Signup to reply.
  2. 0

    The route to solve this is to either first UNION all of the tables together and then PIVOT the results to put the ISP up in the column. OR, alternatively, PIVOT each table to put ISP in the column and then UNION those together. I would opt for the first since PIVOT is expensive computationally so performing that step once would be best

    This would look something like:

    SELECT tablename,
       COUNT(CASE WHEN ISP = 'gmail' THEN email END) as gmail,
       COUNT(CASE WHEN ISP = 'msft' THEN email END) as msft,
       COUNT(...
       COUNT(...
FROM
    (
        SELECT 't1' as tablename, my_isp, email FROm table1 
        UNION ALL
        SELECT 't2', my_isp, email FROM table2
        UNION ALL
        SELECT 't3', my_isp, email FROM table3
        UNION ALL
        SELECT....
    ) sub
GROUP BY tablename
ORDER BY tablename

    dbfiddle example here

    I haven’t looked at your linked question, but I’m sure this was discussed at length, and I feel that it needs to be added here as well since this UNION solution is a band-aid on the actual problem: Namely, all of this data should be in a single table already. A proper schema would essentially look like the output of the subquery in this answer. That may not be possible if you aren’t in charge of the schema or data acquisition, but it’s food for thought. Changing the schema would reduce the complexity of this query as a subquery would be unnecessary, and it would reduce the compute costs of running this thing as the data would already be properly unioned.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search