I want to query the no of transaction done by a customer in a particular year, but the output should come year wise for each customer in table format

Output should be in below format, but I am getting wrong output:
Where 2019,2020,2021 column contains transaction done by customer in respectively 2019, 2020, 2021. Also if transactions in 2019,2020,2021 is equal Max_transaction is populated with first non-zero transaction year .

customer_name    2019   2020 2021  Max_transaction_year  total_transaction
pug                2    1      0        2019                 4
hari               0    1      1        2020                 2
adh                0    0      1        2021                 1

Sample table and data :

Also note that the first two digits in “tid” represent the year of transaction. Eg: 19597 -’19’ represents 2019 and so on for 2020 and 2021.

create table client (cid int,cname char(10));
create table trans (tid int,cid int);
insert into client values(102,'pug'),(107,'ravi'),(109,'hari'),(105,'pon'),(106,'adh'),(104,'bav'),(101,'kat');
insert into trans values(19597,102),(19567,102),(20325,109),(21789,106),(17432,106),(21786,109),(20302,102),(17301,103);


Thanks in advance

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Schema (MySQL v8.0)

create table client (cid int,cname char(10));
create table trans (tid int,cid int);
insert into client values(102,'pug'),(107,'ravi'),(109,'hari'),(105,'pon'),(106,'adh'),(104,'bav'),(101,'kat');
insert into trans values(19597,102),(19567,102),(20325,109),(21789,106),(17432,106),(21786,109),(20302,102),(17301,103);

Query #1

SELECT 
    customer_name,
    SUM(
        CASE WHEN year=2019 THEN no_transactions ELSE 0 END
    ) as '2019',
    SUM(
        CASE WHEN year=2020 THEN no_transactions ELSE 0 END
    ) as '2020',
    SUM(
        CASE WHEN year=2021 THEN no_transactions ELSE 0 END
    ) as '2021',
    MAX(
        CASE WHEN rn=1 THEN year ELSE 0 END
    ) as Max_transaction_year,
    SUM(no_transactions) as total_transaction
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY customer_name ORDER BY no_transactions DESC) rn
    FROM (
        SELECT
            c.cname as customer_name, 
            2000+FLOOR(tid / 1000) as year , 
            COUNT(1) as no_transactions
        FROM
            trans t
        INNER JOIN
            client c ON t.cid = c.cid
        WHERE 
            FLOOR(tid / 1000) BETWEEN 19 and 21
        GROUP BY
            c.cname, 2000+FLOOR(tid / 1000) 
    ) p1
) p2
GROUP BY customer_name;
customer_name201920202021Max_transaction_yeartotal_transaction
adh00120211
hari01120202
pug21020193

View on DB Fiddle

Method 2

I think a somewhat simpler method just uses conditional aggregation:

select cname, cnt_2019, cnt_2020, cnt_2021,
       (case greatest(cnt_2019, cnt_2020, cnt_2021)
            when cnt_2019 then '2019'
            when cnt_2020 then '2020'
            when cnt_2021 then '2021'
        end) as max_year,
       total_transactions
from (select c.cname, c.cid,
             sum(year = '2019') as cnt_2019,
             sum(year = '2020') as cnt_2020,
             sum(year = '2021') as cnt_2021,
             count(*) as total_transactions
      from client c join
           (select t.*, concat('20', left(t.tid, 2)) as year
            from trans t
           ) t
           on c.cid = t.cid
      where year >= '2019' and year <= '2021'
      group by c.cname, c.cid
     ) ct
order by cname, cid;

Here is a db<>fiddle.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x