Version 5.0.19-log of MySQL
Running on Win-XP Pro PC, in case that’s of use
I have an ecard site, and ecard data is stored in MySQL.
When an ecard is read, “readflag” changes from 0 to 1.
I am trying to write an SQL statement to work out what number of cards are read / not read for each email domain name.
Looking on google, I can get the email domain via:
SELECT
SUBSTRING_INDEX('that@hotmail.com','@',-1) domain
FROM DUAL;
I can then use that to work out the number that have been read:
SELECT
COUNT(*),
SUBSTRING_INDEX(recipients_email,'@',-1) domain
FROM ecard
WHERE readflag = 1
GROUP BY SUBSTRING_INDEX(recipients_email,'@',-1)
ORDER BY 1 DESC;
And the number that haven’t been read:
SELECT
COUNT(*),
SUBSTRING_INDEX(recipients_email,'@',-1) domain
FROM ecard
WHERE readflag = 1
GROUP BY SUBSTRING_INDEX(recipients_email,'@',-1)
ORDER BY 1 DESC;
I’m not sure if it’s possible to do a single SQL that combines the 2 statements.
I tried a UNION but it doesn’t do what I’m after:
SELECT COUNT(*) not_read_ct,
SUBSTRING_INDEX(recipients_email,'@',-1) domain
FROM ecard
WHERE readflag = 0
AND SUBSTRING_INDEX(recipients_email,'@',-1) = 'hotmail.com'
GROUP BY SUBSTRING_INDEX(recipients_email,'@',-1)
UNION
SELECT COUNT(*) read_ct,
SUBSTRING_INDEX(recipients_email,'@',-1) domain
FROM ecard
WHERE readflag = 1
AND SUBSTRING_INDEX(recipients_email,'@',-1) = 'hotmail.com'
GROUP BY SUBSTRING_INDEX(recipients_email,'@',-1);
Output:
not_read_ct domain
------------------------------------
20 hotmail.com
50 hotmail.com
What I’d really like is something like:
domain not_read_ct read_ct
-----------------------------------------------
hotmail.com 20 50
Any advice much appreciated
Thanks!