Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2011
    Posts
    34

    Exclamation Unanswered: SQL Server 2005 - Multiple COUNTS with JOINS and WHERE clause HELP

    I work in Marketing and we are trying to do an inventory on how many customers have opted out from being contacted by email, telephone and mail in Canada.

    Is it possible to display the unique optout totals in 1 query

    tele_optout mail_optout email_optout
    ----------- ----------- -------------
    42,997 101,236 72,116


    This is where I am stuck at
    fyi: outout columns are 1 byte flag - either 'Y' or 'N'

    SELECT
    COUNT(OPTOUT_TELE) AS tele_optout,
    COUNT(OPTOUT_EMAIL) AS email_optout,
    COUNT(OPTOUT_MAIL) AS mail_optout
    FROM
    CUST,
    LOC
    WHERE
    (LOC.LOC_ID=CUST.LOC_ID )
    AND (LOC.COUNTRY = 'CANADA')
    AND (CUST.OPTOUT_TELE = 'Y'
    OR CUST.OPTOUT_EMAIL = 'Y'
    OR CUST.OPTOUT_MAIL = 'Y');

    OUTPUT:

    tele_optout mail_optout email_optout
    ----------- ----------- -------------
    42,997 42,997 42,997

    Anyone have any advice?
    Thanks

  2. #2
    Join Date
    Aug 2011
    Location
    Glasgow, UK
    Posts
    36
    You could do it using a "CASE, WHEN, THEN" statement within the SELECT clause then sum your totals. Note that I've used the "newer" style join statements as joining on the where clause has been depricated by Microsoft and may not be supported in future versions of MSSS.

    For Example:
    SELECT b.loc_id, SUM(CASE WHEN a.tele_optout = 'Y' THEN 1 ELSE 0 END) tele_optout,
    SUM(CASE WHEN a.email_optout = 'Y' THEN 1 ELSE 0 END) email_optout,
    SUM(CASE WHEN a.mail_optout = 'Y' THEN 1 ELSE 0 END) mail_optout
    FROM CUST a
    inner join
    LOC b
    ON a.loc_id = b.loc_id
    AND b.country = 'CANADA'
    GROUP BY b.loc_id

    Cheers,
    Marc
    Last edited by marc_; 08-31-11 at 11:45. Reason: Typo

  3. #3
    Join Date
    Aug 2011
    Posts
    34

    thanks

    Thanks a lot,

    Worked perfectly with some tweaking!

Posting Permissions

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