CODEWITHSUNDEEP
sql-serverdatabasejoinsum
SQLNUB
SQLNUB

Reputation: 31

How to do two separate sum results both in separate columns

I have this issue and I'm just not sure if this is even possible. I'm fairly new to MSSQL still.

What I am trying to accomplish is show the biographical data of customers and their rewards earnings over a 12 month period and a 6 month period in the same result so showing the sums in their own dedicated columns.

I can accomplish this separately by running each query on its own and changing my Where criteria time but I cannot figure out how to get independent results for each column for the customer. I just don't know how to make my where statements apply to only the summed columns independently. I tried a sub query for the second 12 month column but it returns more than 1 result which is not allowed and I need multiple results. The following is what I have so far with the 6 month and 12month column and no sub query , second where statement:

SELECT distinct dbo.tCustomerCard.Acct, dbo.tCustomer.FirstName, dbo.tCustomer.LastName, dbo.tCustomerAddress.Address1, dbo.tCustomerAddress.Address2, dbo.tCustomerAddress.City, dbo.tCustomerAddress.StateName, dbo.tCustomerAddress.PostalCode, dbo.tCustomerPhone.PhoneNumber, tCustomerEmail.EmailAddress, tCustomerAttributeDtl.BirthDt, tCustomerBalanceDtl.Ptsbal, dbo.tCustomer.ClubState, 
Sum(dbo.1Day.BasePts) as "6mnth_Tier_Points_Earned", Sum(dbo.1Day.BasePts) as "12mnth_Tier_Points_Earned"


FROM dbo.tCustomer (NOLOCK)

join dbo.tCustomerCard 
on dbo.tCustomer.CustomerId = tCustomerCard.CustomerId 

full outer join dbo.tCustomerAddress 
on dbo.tCustomer.CustomerId = tCustomerAddress.CustomerId 

full outer join dbo.tCustomerPhone 
on dbo.tCustomer.CustomerId = dbo.tCustomerPhone.CustomerId 

full outer join dbo.tCustomerEmail 
on dbo.tCustomer.CustomerId = dbo.tCustomerEmail.CustomerId 

full outer join dbo.tCustomerAttributeDtl 
on dbo.tCustomer.CustomerId = dbo.tCustomerAttributeDtl.CustomerId 

full outer join dbo.tCustomerBalanceDtl 
on dbo.tCustomer.CustomerId = dbo.tCustomerBalanceDtl.CustomerId 

full outer join dbo.1Day 
on dbo.tCustomerCard.Customerid = dbo.1Day.CustomerId 


WHERE dbo.tCustomerAddress.ContactTypeId = dbo.tCustomer.MailingContactTypeID

---below is commented out because I use this for the 12month column back and forth. --and dbo.1Day.PeriodBeginDtm > = (select convert(varchar(4), getdate(),120)) and dbo.1Day.PeriodBeginDtm > = DATEADD(month, -6, GETDATE())

group by (dbo.tCustomerCard.Acct), (dbo.tCustomer.FirstName), (dbo.tCustomer.LastName), (dbo.tCustomerAddress.Address1), (dbo.tCustomerAddress.Address2), (dbo.tCustomerAddress.City), (dbo.tCustomerAddress.StateName), (dbo.tCustomerAddress.PostalCode), (dbo.tCustomerPhone.PhoneNumber), (tCustomerEmail.EmailAddress), (tCustomerAttributeDtl.BirthDt), (tCustomerBalanceDtl.Ptsbal), (dbo.tCustomer.ClubState)

Upvotes: 3

Views: 81

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

You can just use a case expression like this:

SELECT distinct 
  cc.Acct, 
  c.FirstName, 
  c.LastName, 
  ca.Address1, ca.Address2, ca.City, ca.StateName, ca.PostalCode, 
  cp.PhoneNumber, ce.EmailAddress, 
  cad.BirthDt, 
  cbd.Ptsbal, c.ClubState, 
  SUM(CASE 
        WHEN d.PeriodBeginDtm > = (select convert(varchar(4), getdate(),120)) 
        THEN d.BasePts 
      ELSE 0 END ) AS "12mnth_Tier_Points_Earned",
  SUM(CASE 
        WHEN d.PeriodBeginDtm > = DATEADD(month, -6, GETDATE()) 
        THEN d.BasePts 
       ELSE 0 END) AS "6mnth_Tier_Points_Earned"
FROM dbo.tCustomer (NOLOCK) AS c
join dbo.tCustomerCard                    AS cc  on c.CustomerId  = cc.CustomerId 
full outer join dbo.tCustomerAddress      AS ca  on c.CustomerId  = ca.CustomerId 
full outer join dbo.tCustomerPhone        AS cp  on c.CustomerId  = cp.CustomerId 
full outer join dbo.tCustomerEmail        AS ce  on c.CustomerId  = ce.CustomerId 
full outer join dbo.tCustomerAttributeDtl AS cad on c.CustomerId  = cad.CustomerId 
full outer join dbo.tCustomerBalanceDtl   AS cbd on c.CustomerId  = cbd.CustomerId 
full outer join dbo.1Day                  AS d   on cc.Customerid = d.CustomerId 
WHERE ca.ContactTypeId = c.MailingContactTypeID 
group by ca.Acct, c.FirstName, c.LastName, 
         ca.Address1, ca.Address2, ca.City, 
         ca.StateName, ca.PostalCode, cp.PhoneNumber, 
         ce.EmailAddress, cad.BirthDt, cbd.Ptsbal, 
         c.ClubState

Upvotes: 1

Related Questions