---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
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 theWHERE
clause in the third query. BothWHERE
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 aGROUP 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 theDISTINCT
and theAVG
) but without knowing what your end goal is, it's difficult to fully answer your question.