CODEWITHSUNDEEP
mysqlsqlsql-server
m00
m00

Reputation: 286

How to find Percentage using Counts in SQL Server

I have two tables, one for library members and the other for the loans that were made. The tables are joined on the library membership numbers and I need to find the percentage of members that borrowed at least one book from the library.

I already know that there are 645 distinct membership numbers in the Loans table and that there are 2000 distinct membership numbers in the Members table. With that said, I am getting a result of 0 when I run the following query:

SELECT 
    COUNT(DISTINCT L.Member_no) / COUNT(M.Member_no) as 'Percentage'
FROM 
    Reservations.Loan AS L
JOIN
    Members.Member AS M ON L.Member_No = M.Member_no;

Any ideas?

Upvotes: 0

Views: 187

Answers (2)

Ying Li
Ying Li

Reputation: 2519

This should work:

select COUNT(distinct L.Member_no) * 100 / COUNT(distinct M.Member_no) as 'Percentage' from Reservations.Loan as L, Members.Member as M

1) You want percentage, so you need to multiply the numerator by 100, otherwise you get a decimal.

2) If you join the tables Member_no, then you have only one table, counting them and dividing them will give you 100%

===

You can test this by trying these two queries out here

Query 1:

select COUNT(distinct O.CustomerID) * 100 / COUNT(distinct C.CustomerID) as 'Percentage' from Orders as O, Customers as C

Query 1:

select COUNT(distinct O.CustomerID) * 100 / COUNT(distinct C.CustomerID) as 'Percentage' from Orders as O join Customers as C on O.CustomerID = C.CustomerID

Edit: I just realized that my method only works in MySQL, in MSSQL apparently you need the conversion.

Upvotes: 0

IVNSTN
IVNSTN

Reputation: 9309

select CAST(COUNT(distinct L.Member_no) AS NUMERIC(10, 2)) / COUNT(M.Member_no) as 'Percentage'

That is because of integer arithmetic. Convert to numeric with decimals and it will work.

Upvotes: 1

Related Questions