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

Mysql – List all emails and ISP and get count for each PROPERTY

I have a list of emails that can belong to multiple properties in a single table.

Each email has an associated ISP, such as Gmail, Yahoo, etc.

I am trying to count how many properties an email may fall under.

In Excel, I can create a pivot table that looks how I’d like the MySQL results to look like.

Here is an example of the pivot table:

Row Labels prop1 prop2 prop3 prop4 prop5 grand total
email1 1 1 1 3
email2 1 1
email3 1 1 1 1 4
email4 1 1

So the above is what I am trying to achieve in MySQL. Granted, I need to include the ISP, which will be Gmail, Yahoo, etc.

The database table looks something like this:

EMAIL PROPERTY ISP
email1 prop1 gm
email2 prop2 yh
email1 prop4 gm
email4 prop3 ot

I need to create what I did in the pivot table but in MySQL.

  • I need to list out the PROPERTY as the headers
  • the rows are the emails and ISP (gmail, yahoo, etc)
  • then put a 1 if the email is listed under that particular property
  • tally up the results

Can this be even achieved in MySQL? If so, how would I go about writing that query?

I obviously can list out the emails by writing:

SELECT `email` FROM `list_all`

That will start my email rows. I’m just not sure how to get the properties as the headers and then mark if the email is listed under that property.

Here is my attempt to write this query:

SELECT email ,
    GROUP_CONCAT(CASE WHEN `property` = 'PROP1' THEN `isp_group` END) as 'PROP1',
    GROUP_CONCAT(CASE WHEN `property` = 'PROP2' THEN `isp_group` END) as 'PROP2',
    GROUP_CONCAT(CASE WHEN `property` = 'PROP3' THEN `isp_group` END) as 'PROP3',
    GROUP_CONCAT(CASE WHEN `property` = 'PROP4' THEN `isp_group` END) as 'PROP4',
    SUM(CASE WHEN `property` IN ('PROP1','PROP2','PROP3','PROP4') THEN 1 ELSE 0 END) AS TOTAL
FROM
   `list_all`
WHERE `status` = 'ACTIVE'
GROUP BY = `email`

Doing the above yields the following results:

email prop1 prop2 prop3 prop4 total
email1 null null null null 0
email2 null null null null 0
email3 null null null null 0
email4 null null null null 0

Everything is showing up as null. This is very close to what I’ve been looking for. I just need to get rid of the nulls and replace them with either 1 or 0, and then get the totals to add up.

Tags:

2

Answers


  1. 0

    Hard to know, since the question is not precise, but you probably want conditional aggregation. It seems like you attempted that, but in order for aggregation to work (of any kind) you also need a GROUP BY clause.

    Maybe something like this:

    SELECT email,
    COALESCE(GROUP_CONCAT(CASE WHEN `property` = 'PROP1' THEN isp_group END),'') as PROP1,
    COALESCE(GROUP_CONCAT(CASE WHEN `property` = 'PROP2' THEN isp_group END),'') as PROP2,
    COALESCE(GROUP_CONCAT(CASE WHEN `property` = 'PROP3' THEN isp_group END),'') as PROP3,
    COALESCE(GROUP_CONCAT(CASE WHEN `property` = 'PROP4' THEN isp_group END),'') as PROP4,
    COALESCE(GROUP_CONCAT(CASE WHEN `property` = 'PROP5' THEN isp_group END),'') as PROP5,
    SUM(CASE WHEN `property` IN ('PROP1', 'PROP2', 'PROP3', 'PROP4', 'PROP5') THEN 1 ELSE 0 END) As Total
FROM `list_all`
WHERE `status` = 'ACTIVE'
GROUP BY email
    Login or Signup to reply.
  2. 0

    First:
    MySql Doesn’t has Pivot() table function like Sql Server has (in its new versions) but:

    1- You can add some external plugin tools to it.

    2- You can Use a select statement in combination with multi [case when] and an aggregation operator.

    SELECT Subscription_plan,
SUM(CASE
  WHEN Date = STR_TO_DATE('06/01/2023','%m/%d/%Y') THEN Subscribed_customers ELSE 0 END
) AS '06/01/2023',
SUM(CASE
  WHEN Date = STR_TO_DATE('06/02/2023','%m/%d/%Y') THEN Subscribed_customers ELSE 0 END
) AS '06/02/2023',
SUM(CASE
  WHEN Date = STR_TO_DATE('06/07/2023','%m/%d/%Y') THEN Subscribed_customers ELSE 0 END
) AS '06/07/2023'
FROM customers
GROUP BY Subscription_plan;

    3- It exists in PostgreSQL, here is how the pivot tables are created:

    SELECT * 
FROM crosstab('select Subscription_plan, Date, Subscribed_customers from customers ORDER BY 1,2') AS ct (
Subscription_plan varchar(50),
"06/01/2023" int,
"06/02/2023" int,
"06/03/2023" int,
"06/04/2023" int,
"06/05/2023" int,
"06/06/2023" int,
"06/07/2023" int
);

    Second: to get ISP use Patindex to define the ‘@’ positon number then use it inside Substring() like this:

    select * , SUBSTRING(email,patindex('%@%.%', email),LEN(email)) isp 
from (
select '[email protected]' email, 'daff' name
union all 
select '[email protected]' email, 'ibrahim' name
) a

    Third: to handle Null propblem do something like this code:

    SELECT email ,
    (CASE WHEN ISNULL(property,'') = 'PROP1' THEN 1 else '' END) as 'PROP1',
    (CASE WHEN ISNULL(property,'') = 'PROP2' THEN 1 else '' END) as 'PROP2',
    (CASE WHEN ISNULL(property,'') = 'PROP3' THEN 1 else '' END) as 'PROP3',
    (CASE WHEN ISNULL(property,'') = 'PROP4' THEN 1 else '' END) as 'PROP4',
    SUM(CASE WHEN ISNULL(property,'') IN ('PROP1','PROP2','PROP3','PROP4') THEN 1 ELSE 0 END) AS TOTAL
FROM
   emlist
GROUP BY email,property

    More details and other ways explained in the reference:

    https://popsql.com/learn-sql/sql-server/how-to-use-sql-pivot-function

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

Back To Top
Search