CODEWITHSUNDEEP
sqlms-accesscountpercentage
bov25
bov25

Reputation: 121

Calculate Count as Percentage

I have looked around but I just can't seem to understand the logic. I think a good response is here, but like I said, it doesn't make sense, so a more specific explanation would be greatly appreciated.

So I want to show how often customers of each ethnicity are using an credit card. There are different types of credit cards, but if the CardID = 1, they used cash (hence the not equal to 1 statement).

I want to Group By ethnicity and show the count of transactions, but as a percentage. 

SELECT Ethnicity, COUNT(distinctCard.TransactionID) AS CardUseCount
FROM (SELECT DISTINCT TransactionID, CustomerID FROM TransactionT WHERE CardID <> 1) 
AS distinctCard INNER JOIN CustomerT ON distinctCard.CustomerID = CustomerT.CustomerID
GROUP BY Ethnicity
ORDER BY COUNT(distinctCard.TransactionID) ASC

So for example, this is what it comes up with:

Ethnicity | CardUseCount
0         | 100
1         | 200
2         | 300
3         | 400

But I would like this:

Ethnicity | CardUsePer
0         | 0.1
1         | 0.2
2         | 0.3
3         | 0.4

Upvotes: 1

Views: 121

Answers (4)

user783388
user783388

Reputation:

If you need the percentage of card-transaction per ethnicity, you have to divide the cardtransactions per ethnicity by the total transactions of the same ethnicity. You don't need a sub query for that:

SELECT Ethnicity, sum(IIF(CardID=1,0,1))/count(1) AS CardUsePercentage
FROM TransactionT     
INNER JOIN CustomerT 
      ON TransactionT.CustomerID = CustomerT.CustomerID
GROUP BY Ethnicity

Upvotes: 1

odyd
odyd

Reputation: 194

I think the answer you posted is your answer. As they said in your comments , you just count the transactions, you need to divide it by the number of total transactions. As stated in the answer, you need to divide the count(...) by the total number. This would be done as follows:

 SELECT Ethnicity, COUNT(distinctCard.TransactionID)/(SELECT COUNT(TransactionT.TransactionID)
                                                      FROM TransactionT WHERE CardID <> 1)
    AS CardUsePercent
FROM (SELECT DISTINCT TransactionID, CustomerID FROM TransactionT WHERE CardID <> 1) 
AS distinctCard INNER JOIN CustomerT ON distinctCard.CustomerID = CustomerT.CustomerID
GROUP BY Ethnicity
ORDER BY COUNT(distinctCard.TransactionID) ASC

This will give the result you want.

EDIT: This may be wrong, as i dont know the exact format of your tables, but i was assuming that the TransactionID field is Unique in the table. Else use the DISTINCT keyword, or the PK of your table , depending on your actual implemetation

Upvotes: 0

Barnaby Mercer
Barnaby Mercer

Reputation: 67

SELECT Ethnicity, COUNT(distinctCard.TransactionID) / (SELECT COUNT(1) FROM TransactionT WHERE CardID <> 1) AS CardUsePer
FROM (SELECT DISTINCT TransactionID, CustomerID FROM TransactionT WHERE CardID <> 1) 
AS distinctCard INNER JOIN CustomerT ON distinctCard.CustomerID = CustomerT.CustomerID
GROUP BY Ethnicity
ORDER BY COUNT(distinctCard.TransactionID) ASC

Upvotes: 0

Rahul
Rahul

Reputation: 77926

From your posted sample result to me it looks like you just wanted to divide the count by 1000 like

SELECT Ethnicity, 
COUNT(distinctCard.TransactionID) / 1000 AS CardUseCount
FROM <rest part of query>

Upvotes: 0

Related Questions