SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Evangelist
    Join Date
    Dec 2004
    Posts
    419
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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:

    Code:
    SELECT 
       SUBSTRING_INDEX('that@hotmail.com','@',-1) domain
    FROM DUAL;
    I can then use that to work out the number that have been read:

    Code:
    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:

    Code:
    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:

    Code:
    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:

    Code:
    not_read_ct       domain
    ------------------------------------
    20               hotmail.com
    50               hotmail.com
    What I'd really like is something like:

    Code:
    domain           not_read_ct       read_ct
    -----------------------------------------------
    hotmail.com      20                50
    Any advice much appreciated

    Thanks!

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Code sql:
    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
    Last edited by ScallioXTX; May 24, 2010 at 13:22. Reason: changed "variable"
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  3. #3
    SitePoint Evangelist
    Join Date
    Dec 2004
    Posts
    419
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you! That works brilliantly.
    Simple is best - no messy stuff in your solution.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •