CODEWITHSUNDEEP
sqlgroup-by
DDiVita
DDiVita

Reputation: 4265

SQL Distinct Grouping?

Here is my query:

SELECT 
  dbo.EmailCampaignTracking.emailOpened,
  dbo.EmailCampaignTracking.emailUnsubscribed,
  dbo.EmailCampaignTracking.emailBounced,
  COUNT(*)
FROM
  dbo.EmailCampaignTracking
  Group By
  dbo.EmailCampaignTracking.emailBounced,
  dbo.EmailCampaignTracking.emailUnsubscribed,
  dbo.EmailCampaignTracking.emailOpened

Here is what my results look like:

    emailOpened emailUnsubscribed emailBounced Totals

    True          False            False          6      
    False         False            True           1   
    True          True             False          2

I would like the total for email opend to be 8 and not 6. I realize i am asking SQL to group them that way. I just want to know how I can get Distict totals for each column. So, emailOpened would be 8, emailUnsubscribed would be 1, and emailBounced would be 2. Thanks

Upvotes: 0

Views: 200

Answers (2)

Scott Arrington
Scott Arrington

Reputation: 12503

I would assume you only want to count the records where the values are set to true. If so then this might work:

SELECT SUM(CAST(EmailOpened AS INT)) AS OpenedEmails,
  SUM(CAST(EmailUnsubscribed AS INT)) AS UnsubscribedEmails,
  SUM(CAST(EmailBounced AS INT)) AS EmailBounced
FROM dbo.EmailCampainTracking

Upvotes: 2

shahkalpesh
shahkalpesh

Reputation: 33476

SELECT Count(emailOpened) AS OpenedCount,
Count(emailUnsubscribed) as UnsubCount,
Count(emailBounced) as BouncedCount
FROM dbo.EmailCampaignTracking

EDIT: If you are looking to count records when the flag is true, you could write it as below

SELECT SUM(CASE emailOpened WHEN true then 1 else 0) AS OpenedCount,
SUM(CASE emailUnsubscribed WHEN true then 1 else 0) as UnsubCount,
SUM(CASE emailBounced WHEN true then 1 else 0) as BouncedCount
FROM dbo.EmailCampaignTracking

EDIT2: In above query, replace true with the bit value that is interpreted as true (it could be 0).

Upvotes: 5

Related Questions