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

MySQL count the number of count of email occurrences separated by countries

I need to compose a MySQL query, which will count the number of email occurrences per each country

I have following mysql table (shop_orders)

order_id email country
1 [email protected] _it
2 [email protected] _hu
3 [email protected] _de
4 [email protected] _pl
5 [email protected] _it

Desired output

count _it _hu _de _pl
1 0 1 1 1
2 2 0 0 0

So far I have managed to do is fetch the count, how many time email appears.

SELECT 
    COUNT(*) AS count, email
FROM
    pinkpanda.shop_orders
GROUP BY email
ORDER BY count DESC;

But now I need to count off all the counts of emails and separate the data for each country

If you need any additional informations, please let me know and I will provide.

Tags:

2

Answers


  1. 0

    Please confirm that everything is right for you, and let me know if more adjustment is necessary.

    SELECT country, email, COUNT(*) AS occurrences
FROM shop_orders
GROUP BY country, email
ORDER BY country, occurrences DESC

    Output:
    enter image description here

    Login or Signup to reply.
  2. 0

    You can use conditional aggregation to group data by the number of emails per country:

    with cte as (
  SELECT 
    COUNT(*) AS count_, email, country
  FROM
    shop_orders
  GROUP BY email, country
  ORDER BY count_ DESC
)
select count_, 
       sum(case when country = '_it' then 1 else 0 end) as _it,
       sum(case when country = '_hu' then 1 else 0 end) as _hu,
       sum(case when country = '_de' then 1 else 0 end) as _de,
       sum(case when country = '_pl' then 1 else 0 end) as _pl
from cte
group by count_

    This is a valid solution for old mysql :

    select count_, 
       sum(case when country = '_it' then 1 else 0 end) as _it,
       sum(case when country = '_hu' then 1 else 0 end) as _hu,
       sum(case when country = '_de' then 1 else 0 end) as _de,
       sum(case when country = '_pl' then 1 else 0 end) as _pl
from (
  SELECT 
    COUNT(*) AS count_, email, country
  FROM
    shop_orders
  GROUP BY email, country
  ORDER BY count_ DESC
) cte
group by count_

    Demo here

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

Back To Top
Search