CODEWITHSUNDEEP
sqlsql-servercount
Edward Gizbreht
Edward Gizbreht

Reputation: 306

SQL query with count and left join

I have a query, where i need to calculate 'userID' from 2 tables.

SQL QUERY:

    SELECT DISTINCT TOP 1000 u.id as userID, u.firstName as userFirstName, u.email as userEmail, u.phone as userPhone, 
count(ueo.userID) as opensEmailCounter, count(ush.userID) as opensSmsCounter
    FROM dbo.Users u
    LEFT JOIN dbo.UserEmailsOpens ueo ON u.id = ueo.userID AND ueo.targetID = 4
    LEFT JOIN dbo.UserSmsHistory ush ON u.id = ush.userID AND ush.targetID = 4 AND ush.opened = 1
    WHERE u.deleted = 0
    AND IsNull(u.firstName, '') != '' 
    AND IsNull(u.email, '') != '' 
    AND IsNull(u.phone, '') != ''
GROUP BY u.id, u.firstName, u.email, u.phone

But, the result is not what i expected. It giving to me the wrong numbers after i did the second left join. It twice my result in some cases and showing the same count result (attached the screenshot). enter image description here

Upvotes: 1

Views: 1280

Answers (2)

India.Rocket
India.Rocket

Reputation: 1245

Modified your query a bit. Used Case Whento eliminate counting of blanks from the result.

Select userID, userFirstName, userEmail, userPhone, 
sum(case when ueo_userID <> '' then 1 else 0 end) as opensEmailCounter,
sum(case when ush_userID <> '' then 1 else 0 end) as opensSmsCounter
from
 (
    SELECT DISTINCT u.id as userID, u.firstName as userFirstName, u.email as userEmail, u.phone as userPhone, 
    ueo.userID as ueo_userID, ush.userID as ush_userID
    FROM dbo.Users u
    LEFT JOIN dbo.UserEmailsOpens ueo ON u.id = ueo.userID AND ueo.targetID = 4
    LEFT JOIN dbo.UserSmsHistory ush ON u.id = ush.userID AND ush.targetID = 4 AND ush.opened = 1
    WHERE u.deleted = 0user
    AND IsNull(u.firstName, '') != '' 
    AND IsNull(u.email, '') != '' 
    AND IsNull(u.phone, '') != ''
 ) a
GROUP BY userID, userFirstName, userEmail, userPhone;

Let me know if you have any questions

Upvotes: 0

SqlZim
SqlZim

Reputation: 38023

Aggregate before the joins like so: 

select distinct top 1000 
    u.id as userID
  , u.firstName as userFirstName
  , u.email as userEmail
  , u.phone as userPhone
  , ueo.opensEmailCounter
  , ush.opensSmsCounter
from dbo.Users u
  left join (
    select 
        userID
      , count(*) as opensEmailCounter
    from dbo.UserEmailsOpens 
    where targetID = 4
    group by userID
    ) ueo
     on u.id = ueo.userID
  left join (
    select 
        userID
      , count(*) as opensSmsCounter
    from dbo.UserSmsHistory 
    where targetID = 4
      and opened = 1
    group by userID
    ) ush
     on u.id = ush.userID
where u.deleted = 0
  and IsNull(u.firstName, '') != ''
  and IsNull(u.email, '')     != ''
  and IsNull(u.phone, '')     != ''

Upvotes: 1

Related Questions