CODEWITHSUNDEEP
c#sql
user1133650
user1133650

Reputation: 1

Create a query to get counts and other columns

I am trying to figure out the best way to write this Sql query. I need to get a total number of promotional emails a customer has read that were sent to them . So we have a table that store customerinformation, a table that stores their Login information and table which stores the links to the different emails sent to the customers and a table that logs the clicks

this is what i have so far. 

Select Customer.CustomerId,
Customer.FirstName,
Customer.LastName,
Customer.email,
Customer.Phone
From Customer
Join LoginInformation
ON customer.CustomerId = LoginInformation.CustomerId
JOIN ProductLinks
ON LoginInformation.LoginId= ProductLinks.LoginId

Now i need to get the total click each customer has made which are in a click table which has the following columns

ClickID
ProductLinkID
LoginId

i need the following output

  Customer.CustomerId,
   Customer.FirstName,
   Customer.LastName,
   Customer.email,
   Customer.Phone,
   Total Clicks   ( this can be a sum of all the porductlinks every clicked)

using SQL and C#

Upvotes: 0

Views: 85

Answers (2)

gbn
gbn

Reputation: 432180

You need this kind of construct. Note the 2 column LEFT JOIN onto Click

Select 
  Cu.CustomerId,
  Cu.FirstName,
  Cu.LastName,
  Cu.email,
  Cu.Phone,
  Count(Ck.LoginId) AS [Total Clicks]
From 
  Customer Cu
  Join 
  LoginInformation LI ON Cu.CustomerId = LI.CustomerId
  JOIN 
  ProductLinks PL ON LI.LoginId= PL.LoginId
  LEFT JOIN 
  Click Ck ON LI.LoginId = Ck.LoginId ON PL.ProductLinkId = Ck.ProductLinkId
GROUP BY 
  Cu.CustomerId,
  Cu.FirstName,
  Cu.LastName,
  Cu.email,
  Cu.Phone;

Upvotes: 1

Ivo
Ivo

Reputation: 8352

This should do the trick:

Select Customer.CustomerId,
Customer.FirstName,
Customer.LastName,
Customer.email,
Customer.Phone,
Count(ClickId) [Total Clicks]
From Customer
Join LoginInformation
ON customer.CustomerId = LoginInformation.CustomerId
JOIN ProductLinks
ON LoginInformation.LoginId= ProductLinks.LoginId
JOIN Click
ON Click.LoginId = LoginInformation.LoginId
GROUP BY Customer.CustomerId,
Customer.FirstName,
Customer.LastName,
Customer.email,
Customer.Phone

Upvotes: 0

Related Questions