How to print the required output with the help of SQL

I have tables ‘users’ and ‘user_transactions’ in AWS Athena. The table has many columns but I am interested in 2 columns viz. user name and txn_type.

The txn_type column has 3 possible values viz. FT, NFT, PT.

For each user, I need to print the count of each transactions in the below format. The output should contain 4 columns with userId, and count of each txns.

User Name, CountOfTxns of type FT, CountOfTxns of type NFT, CountOfTxns of type PT

Morover, I need to join user and user_transactions table for printing records of users who are of type = ‘Gold’

so to find the users, it will be

select userId, userName from users, user_transactions t where u.userId = t=userId and u.type = 'Gold';

Then for these users, I need to print the output with 4 columns as described above.

So if a user with name = ‘ABC’ and Id = 1 and user_type = ‘Gold’ has 3 counts of FT, 5 counts of NFT and 0 counts of PT,

and if a user with name = ‘XYZ’ and Id = 2 and user_type = ‘Gold’ has 9 counts of FT, 0 counts of NFT and 45 counts of PT, the output should be the output should be

User Name, CountOfTxns of type FT, CountOfTxns of type NFT, CountOfTxns of type PT

ABC, 3, 5, 0
XYZ, 9, 0, 45

How can I write a query which can print all counts in a single row?

Answer

You can calculate each field individually like so:

   SELECT u.name,
      SUM(CASE WHEN t.txn_type = 'FT' THEN 1 ELSE 0 END) as FT_count,
      SUM(CASE WHEN t.txn_type = 'NFT' THEN 1 ELSE 0 END) as NFT_count,
      SUM(CASE WHEN t.txn_type = 'PT' THEN 1 ELSE 0 END) as PT_count
    FROM users u, transactions t
    WHERE u.user_id = t.user_id and u.type = 'Gold'
    GROUP BY u.name;