Group / Counting SQL query advice

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!

Thank you! That works brilliantly.
Simple is best - no messy stuff in your solution.


SELECT 
  COUNT(*) AS total, SUM(readflag) AS totalRead,
  SUBSTRING_INDEX(recipients_email,'@',-1) domain
FROM ecard
GROUP BY SUBSTRING_INDEX(recipients_email,'@',-1)

read = totalRead
not read = total - totalRead