Question 60866 in Mysql Question posted in
The official MySQL documentation can be found here.

Issue making 3 columns from Calculations on a single column – Mysql

I have two tables that I am trying to join and display the count of transactions by year grouped by user_name. The data is from two separate tables: users and transactions. I need the output to have four total columns: user_name, 2019, 2020, 2021. The year of the transaction can be identified by the first two digits of the transaction_id: 19ABXY would be from 2019, 20TUZI would be from 2020, etc. Here is what I have so far:

SELECT users.user_name
,    COUNT(case when transactions.transaction_id LIKE '19%'
,    COUNT(case when transactions.transaction_id LIKE '20%'
,    COUNT(case when transactions.transaction_id LIKE '21%'
FROM users
INNER JOIN transactions on users.user_id=transaction.user_id
GROUP BY users.user_name;

This returns a syntax error.

I have looked at other ways to create multiple columns from a single column that generally matches the required commands here, but have not found anything that doesn’t return an error. Any suggestions would be greatly appreciated!

Tags:

2

Answers


  1. 0 
    SELECT users.user_name
 ,    sum(case when transactions.transaction_id LIKE '19%' then 1 else 0 end)as tran_19
,    sum(case when transactions.transaction_id LIKE '20%' then 1 else 0 end)as tran_20
,    sum(case when transactions.transaction_id LIKE '21%' then 1 else 0 end)as tran_21
FROM users
INNER JOIN transactions on users.user_id=transaction.user_id
GROUP BY users.user_name;
    Login or Signup to reply.
  2. 0

    You would at least need to finish your CASE expressions and close the parentheses for each column.

    COUNT(case when transactions.transaction_id LIKE '19%' THEN 1 END)
                                                       ^^^^^^^^^^^

    COUNT() works because the CASE returns NULL if there is no match, and COUNT() ignores NULLs.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search