---below query gives all the customers from fact irrespective of condition

SELECT     count( dbo.Fact_Promotion.customerid) as Mailquantity
FROM         dbo.Fact_Promotion   
INNER JOIN dbo.Dim_Promotion  
    ON dbo.Fact_Promotion.PromotionID = dbo.Dim_Promotion.PromotionID 

---below query gives customers with where condition

SELECT     count(distinct fact_loan.customerid) as [New loans] ,avg(Fact_Loan.Financeamount) as [Avg New Loan Amount]
FROM         dbo.Fact_Promotion 
 where <condition> 
      AND  dbo.Fact_Loan.LoanTypeID = 6 
      AND dbo.Fact_Loan.AccountStatusID = 1

----below query gives customers with different where condition

SELECT     count(distinct fact_loan.customerid) as [Total loans],avg(Fact_Loan.Financeamount) as [Avg Total Loan Amount]
FROM         dbo.Fact_Promotion 
 where <condition>
    AND dbo.Fact_Loan.AccountStatusID = 1
1

There are 1 answers

0
Steve Matthews On

I'm not sure from your question what you are trying to achieve.

The WHERE clause in the second query appears to deliver a subset of the data from the WHERE clause in the third query. Both WHERE statements look identical with the exception that the second query (New loans) includes an extra condition that the LoanTypeId (presumably the financial product they have taken) is 6. I guess this is the latest loan product or campaign.

Without knowing what you're trying to do it's difficult to give you an answer but if you want to show total number of customers by LoanTypeId you could aggregate a count by adding the LoanTypeId column to the SELECT statement and adding a GROUP BY dbo.Fact_Loan.LoanTypeId to the end of the statement.

This may not be a straight forward as that as you're doing some other stuff in your SELECT (such as the DISTINCT and the AVG) but without knowing what your end goal is, it's difficult to fully answer your question.