Reputation: 121
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
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
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
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
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