Beim Versand von Newslettern ist es praktisch zu wissen, welche Email-Anbieter die Kunden verwenden. Die folgende Abfrage extrahiert die Domain des Anbieters und zählt die Email-Adressen der Newsletter-Abonnenten:
SELECT provider_domain AS provider, count(subscriber_email) AS number FROM ( SELECT subscriber_email, SUBSTRING_INDEX(subscriber_email, '@', -1) provider_full, SUBSTRING_INDEX(SUBSTRING_INDEX(subscriber_email, '@', -1), '.', -2) provider_domain FROM newsletter_subscriber ) AS t GROUP BY provider ORDER BY number DESC;
Leicht angepasst, kann diese Abfrage auf alle Kunden angewendet werden:
SELECT provider_domain AS provider, count(email) AS number FROM ( SELECT email, SUBSTRING_INDEX(email, '@', -1) provider_full, SUBSTRING_INDEX(SUBSTRING_INDEX(email, '@', -1), '.', -2) provider_domain FROM customer_entity ) AS t GROUP BY provider ORDER BY number DESC;
Weiter optimiert, fasst die Anfrage Anbieter mit weniger als 50 Email-Adressen in eine Zeile mit dem Eintrag „OTHER“ zusammen:
SELECT provider, SUM(number) number FROM ( SELECT IF(number < 50, '* OTHER', provider) provider, number FROM ( SELECT provider_domain provider, GROUP_CONCAT(DISTINCT provider_full) domains, COUNT(email) AS number FROM ( SELECT email, SUBSTRING_INDEX(email, '@', -1) provider_full, SUBSTRING_INDEX(SUBSTRING_INDEX(email, '@', -1), '.', -2) provider_domain FROM customer_entity ) AS t GROUP BY provider_domain ) AS v ) AS w GROUP BY provider ORDER BY number DESC;
Einige Anbieter-Domains (z.B. gmail und googlemail) sollten noch manuell zusammengefasst werden.
Um auch noch die Summe anzeigen zu lassen, kann WITH ROLLUP verwendet werden. Um die Liste absteigend sortiert zu lassen, ist allerdings eine weitere Schachtelung nötig:
SELECT IF(provider IS NULL, '* TOTAL', provider) provider, number FROM ( SELECT provider, SUM(number) number FROM ( SELECT IF(number < 50, '* OTHER', provider) provider, number FROM ( SELECT provider_domain provider, GROUP_CONCAT(DISTINCT provider_full) domains, COUNT(email) AS number FROM ( SELECT email, SUBSTRING_INDEX(email, '@', -1) provider_full, SUBSTRING_INDEX(SUBSTRING_INDEX(email, '@', -1), '.', -2) provider_domain FROM customer_entity ) AS t GROUP BY provider_domain ) AS v ) AS w GROUP BY provider WITH ROLLUP ) AS x ORDER BY number DESC