SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    May 2009
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need Help With SQL - 2 Columns of Data

    I have a table with lots of columns and from that table I've selected 2 columns of data which can be seen below.

    The 2 columns are:
    user_email and letter

    user_email contains every person's email address and letter column contains a value which is either A or B. Each person's email can appear in any number of rows.

    example: table_X
    Code:
    bob@hotmail.com, B
    bob@hotmail.com, B
    bob@hotmail.com, A
    chris@hotmail.com, A
    chris@hotmail.com, A
    sue@hotmail.com, B
    jess@yahoo.com, A
    jess@yahoo.com, A
    jess@yahoo.com, A
    jess@yahoo.com, B
    I'm trying to find out 3 different values, the number of users (I assume using COUNT) who:
    1. Have both A and B
    2. Have Only B
    3. Have Only A

    This one's stumped me. Any ideas?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT COUNT(CASE WHEN a > 0 AND b > 0 THEN 'bofadem' END) AS bofadem
         , COUNT(CASE WHEN a = 0 AND b > 0 THEN 'only b'  END) AS only_b
         , COUNT(CASE WHEN a > 0 AND b = 0 THEN 'only a'  END) AS only_a
      FROM ( SELECT user_email
                  , COUNT(CASE WHEN letter='A' THEN 'A' END) AS a
                  , COUNT(CASE WHEN letter='B' THEN 'B' END) AS b
               FROM daTable
             GROUP
                 BY user_email ) AS counts
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    Code:
    (
        SELECT 'both' as type, 
               COUNT(lettera.user_email) as number
          FROM (   SELECT 
                 DISTINCT user_email
                     FROM table-with-lots-of-cols
                    WHERE letter = 'A' )
               lettera
    INNER JOIN
               (   SELECT 
                 DISTINCT user_email
                     FROM table-with-lots-of-cols
                    WHERE letter = 'B' )
               letterb
            ON lettera.user_email = letterb.user_email
    )
    UNION ALL
    (
        SELECT 'A' as type, 
               COUNT(lettera.user_email) as number
          FROM (   SELECT 
                 DISTINCT user_email
                     FROM table-with-lots-of-cols
                    WHERE letter = 'A' )
               lettera 
          LEFT
    OUTER JOIN
               (   SELECT 
                 DISTINCT user_email
                     FROM table-with-lots-of-cols
                    WHERE letter = 'B' )
               letterb
            ON lettera.user_email = letterb.user_email   
         WHERE letterb.user_email IS NULL
    )
    UNION ALL
    (
        SELECT 'B' as type, 
               COUNT(letterb.user_email) as number
          FROM (   SELECT 
                 DISTINCT user_email
                     FROM table-with-lots-of-cols
                    WHERE letter = 'A' ) 
               lettera 
         RIGHT
    OUTER JOIN
               (   SELECT 
                 DISTINCT user_email
                     FROM table-with-lots-of-cols
                    WHERE letter = 'B' )
               letterb
            ON lettera.user_email = letterb.user_email   
         WHERE lettera.user_email IS NULL
    )

  4. #4
    SitePoint Member
    Join Date
    May 2009
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a lot to both of you for replying, very good answers, appreciate your time!!! Rudy I went with yours, spot on - majestic! What an expert!

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    majestic? whoa, hold on there

    thanks, though

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •