CODEWITHSUNDEEP
sqlsql-servercount
Brett Holmes
Brett Holmes

Reputation: 387

Counting values for multiple distinct rows in a SQL Query

I'm very new to SQL so please pardon my lack of knowledge. I'm also doing this for a class so please don't just give me the answer, I'd like to learn.

I have 3 tables of which are Contracts, PaymentTypes, InsuranceCompanies. I'm trying to join them and then display the number of contracts whose payment method is insurance, broken out by Insurance Company. I have done so, but it is not counting the amount of contracts per insurance company. It is instead counting how many InsuranceID's that are using Insurance and placing that into each Insurance Company count. Here is what I have for the SQL Query:

Select Distinct InsuranceCompanies.InsuranceCompany, Count (Contracts.InsuranceID)  as 'Number of contracts'
from  Contracts, PaymentTypes, InsuranceCompanies 
where (Contracts.PaymentTypeID = 3) AND (PaymentTypes.PaymentTypeID = 3) AND (Contracts.PaymentTypeID = PaymentTypes.PaymentTypeID) 
Group by InsuranceCompanies.InsuranceCompany

Here is what it outputs:

All Insurance       273
Best Insurance      273
Friendly Insurance  273
Insurance One       273
Safety Insurance    273

So my question is how can I have it count individual Insurance Companies then display it accordingly?

I took the answer given to me, applied all variables needed, and it functions perfectly! Here is the final working Query:

Select i.InsuranceCompany, Count(c.InsuranceID) as 'Number of contracts'
from  Contracts c
join paymenttypes p on p.PaymentTypeID = c.PaymentTypeID
join insurancecompanies i on i.InsuranceID = c.InsuranceID
where c.PaymentTypeID = 3 AND p.PaymentTypeID = 3
Group by i.InsuranceCompany

Upvotes: 0

Views: 224

Answers (1)

davejal
davejal

Reputation: 6133

Your query should look something like this, but as I'm missing parts of your structure I cannot fill in the part after on

Select i.InsuranceCompany, Count(c.InsuranceID) as 'Number of contracts'
from  Contracts c
join paymenttypes p on p.PaymentTypeID = c.PaymentTypeID
join insurancecompanies i on i.InsuranceID = c.InsuranceID
where c.PaymentTypeID = 3 AND p.PaymentTypeID = 3
Group by i.InsuranceCompany

so read about table join

Upvotes: 1

Related Questions