CODEWITHSUNDEEP
sqlt-sqlcount
Nikolas Brown
Nikolas Brown

Reputation: 39

Simply SQL query

I have a SQL script and would like to have multiple counts depending on different where clauses, instead of repeating such as this is there a way I can simplify this?

select
    UnverifiedEmails = 
        (
            select count(distinct c.ContactRef) 
            from ContactEmailAddressVerification c
            LEFT JOIN EmailAddressVerification e
            ON e.EmailAddressVerificationID = c.EmailAddressVerificationID
            WHERE DateVerified IS NULL
        ),
    VerifiedEmails =
        (
            select count(distinct c.ContactRef) 
            from ContactEmailAddressVerification c
            LEFT JOIN EmailAddressVerification e
            ON e.EmailAddressVerificationID = c.EmailAddressVerificationID
            WHERE DateVerified IS NOT NULL
        ),
    LastMonthVerified =
        (
            select count(distinct c.ContactRef) 
            from GDPR_ContactEmailAddressVerification c
            LEFT JOIN EmailAddressVerification e
            ON e.EmailAddressVerificationID = c.EmailAddressVerificationID
            WHERE DateVerified IS NOT NULL
            AND DATEPART(m, e.DateAdded) = DATEPART(m, DATEADD(m, -1, getdate()))
        ),
    LastMonthUnverified =
        (
            select count(distinct c.ContactRef) 
            from ContactEmailAddressVerification c
            LEFT JOIN EmailAddressVerification e
            ON e.EmailAddressVerificationID = c.EmailAddressVerificationID
            WHERE DateVerified IS NULL
            AND DATEPART(m, e.DateAdded) = DATEPART(m, DATEADD(m, -1, getdate()))
        )

Upvotes: 1

Views: 65

Answers (3)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

use case when don't need multiple sub-query

select  count(distinct case when DateVerified IS NULL then  c.ContactRef end) UnverifiedEmails ,
        count(distinct case when DateVerified IS not NULL then  c.ContactRef end) VerifiedEmails,
        count(distinct case when DateVerified IS NOT NULL
                     AND DATEPART(m, e.DateAdded) = DATEPART(m, DATEADD(m, -1, getdate()))
                     then  c.ContactRef end ) LastMonthVerified,
        count(distinct case when DateVerified IS NULL
                     AND DATEPART(m, e.DateAdded) = DATEPART(m, DATEADD(m, -1, getdate()))
                     then  c.ContactRef end) LastMonthUnverified

                from ContactEmailAddressVerification c
                LEFT JOIN EmailAddressVerification e
                ON e.EmailAddressVerificationID = c.EmailAddressVerificationID

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can do aggregation with single SELECT statement :

SELECT COUNT(DISTINCT CASE WHEN DateVerified IS NULL THEN c.ContactRef END) UnverifiedEmails,
       COUNT(DISTINCT CASE WHEN DateVerified IS NOT NULL THEN c.ContactRef END) VerifiedEmails,
       COUNT(DISTINCT CASE WHEN (DateVerified IS NOT NULL AND 
                                 DATEPART(m, e.DateAdded) = DATEPART(m, DATEADD(m, -1, getdate()))
                                ) 
                           THEN c.ContactRef
             END) LastMonthVerified,
       COUNT(DISTINCT CASE WHEN (DateVerified IS NULL AND 
                                 DATEPART(m, e.DateAdded) = DATEPART(m, DATEADD(m, -1, getdate()))
                                ) 
                           THEN c.ContactRef 
             END) LastMonthUnverified 
FROM ContactEmailAddressVerification c LEFT JOIN 
     EmailAddressVerification e
     ON e.EmailAddressVerificationID = c.EmailAddressVerificationID;

Upvotes: 4

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

You may use conditional aggregation:

SELECT
    COUNT(DISTINCT CASE WHEN DateVerified IS NULL
                        THEN c.ContactRef END) AS UnverifiedEmails,
    COUNT(DISTINCT CASE WHEN DateVerified IS NOT NULL
                        THEN c.ContactRef END) AS VerifiedEmails,
    COUNT(DISTINCT CASE WHEN DateVerified IS NOT NULL AND
        DATEPART(m, e.DateAdded) = DATEPART(m, DATEADD(m, -1, GETDATE()))
                        THEN c.ContactRef END) AS LastMonthVerified,
    COUNT(DISTINCT CASE WHEN DateVerified IS NULL AND
        DATEPART(m, e.DateAdded) = DATEPART(m, DATEADD(m, -1, GETDATE()))
                        THEN c.ContactRef END) AS LastMonthUnverified
FROM ContactEmailAddressVerification c
LEFT JOIN EmailAddressVerification e
    ON e.EmailAddressVerificationID = c.EmailAddressVerificationID;

The idea here is to make a single pass over the joined tables, and then take counts/sums conditionally, depending on the logic in each of the WHERE clauses of your original query.

Upvotes: 1

Related Questions