Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- I have a table of accounts and a table of transactions. There's a
- one-to-many relationship between accounts and transactions;
- transactions.account_id is a foreign key into accounts. For all
- accounts where < 50% of transactions are authorized, I want to
- get the account id and that percentage:
- SELECT a.id, SUM(t.authorized=1)/COUNT(*) AS percentage
- FROM accounts a, transaction_info t
- WHERE a.id=t.account_id
- GROUP BY a.id
- HAVING percentage < .5 ORDER BY percentage DESC;
- What if transaction_info.authorized weren't already a boolean?
- Let's say it's a VARCHAR with possible
- values "authorized", "unauthorized", "unknown":
- SELECT a.id, SUM(CASE WHEN l.authorized="authorized" THEN 1 ELSE 0 END)/COUNT(*) AS percentage
- FROM accounts a, transaction_info t
- WHERE a.id=t.account_id
- GROUP BY a.id
- HAVING percentage < .5 ORDER BY percentage DESC;
Add Comment
Please, Sign In to add comment