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

Mysql – Get count of each email type from multiple tables

I have 15+ tables with emails. I need to count how many emails exist for each internet service provider, like GMAIL, VMG, MSFT, or Other.

Using the below query, I can get a count of the ISPs for one table:

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

This will spit out something like this:

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

What I need to do is get a total count of the above per each table, and I have over 15 tables.

I’m honestly not exactly sure how to start this.

** EDIT **

All of the tables have the same format with the same columns. They all look like this:

EMAIL | PROPERTY | STATUS | MY_ISP | CREATED_DATE | OTHER_DATES

Some emails will exist in multiple tables. The PROPERTY of each table will be different, as well as the STATUS and Date columns (I did not list all of the date columns).

** EDIT 2 **

So I tried to use a UNION ALL like this:

select COUNT(`my_isp`) AS 'COUNT'
from (select count(t1.`my_isp`) from `table1` t1
    UNION ALL
    select count(t2.`my_isp`) from `table2` t2
   )

But I get the below error:

#1248 - Every derived table must have its own alias
Tags:

2

Answers


  1. 0

    Your union all attempt was close. You want to return the my_isp from each table.

    The "Every derived table must have its own alias" error is telling you that when you select from or join a subquery, you need to give it an alias (as somealias or just somealias after the close paren).

    Additionally, you are misunderstanding COUNT; the expression you give to count is only tested to see if it is NULL, and count returns the number of non-nulls for each group; if you omit a group by clause, the group is all the rows. For this case, you want just count(1) but to also group by and select the my_isp.

    So:

    select my_isp, count(1) as isp_count
from (
    select my_isp from table1
    union all
    select my_isp from table2
    ...
) as unioned_tables
group by my_isp

    It may be more efficient to count within each table and sum them instead:

    select my_isp, sum(isp_count) as isp_count
from (
    select my_isp, count(1) as isp_count from table1 group by my_isp
    union all
    select my_isp, count(1) as isp_count from table2 group by my_isp
    ...
) as unioned_table_counts
group by my_isp
    Login or Signup to reply.
  2. 0

    Without touching on the issue of the correct organization of tables (or one table), you can specify that the error

    #1248 - Every derived table must have its own alias

    is caused by the absence of a subquery name.
    This is too simple a mistake to discuss)

    Actually, the request can be 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
    my_isp total
    google 3
    yahoo 2
    other 1

    fiddle

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

Back To Top
Search