[SOLVED] Mysql query to get the distinct count by current year and last year on the same date

Mysql query to get the distinct count by current year and last year on the same date

I am trying to get the distinct count of (ordno,partno) by the date for current year and last year..Please find below the DDL for the input and the output tables.

Create table input
(ordno varchar(20),
partno varchar(40),
orddate date)

insert into input values
('BC132','13211','4/1/2022'),
('NM121','8901','4/1/2022'),
('JK121','90122','4/1/2022'),
('NM121','9021','4/1/2021'),
('KL123','634','4/1/2021'),
('OP121','453','4/1/2021'),
('KL121','6781','4/1/2021'),
('KL1234','93211','4/15/2022'),
('OQ121','431','4/15/2022'),
('HJ121','321','4/15/2022'),
('M213','221','4/15/2022'),
('B121','901','4/15/2022')


Create table output
(orddate date,
currentyear int,
last year int)

insert into output values
('Apr 01',3','4'),
('Apr 15',3','2')

Thanks, Arun

Solution

Your expected result and create table statement and data examples doesn't match each other.

I used proper date datatype and used DATE_FORMAT to give your expected format.

Try:

select  DATE_FORMAT(orddate,'%b %d') as my_date,
       count(case when YEAR(orddate)='2022' then partno  end) as currentyear,
       count(case when YEAR(orddate)='2021' then partno  end) as last_year  
from input
group by my_date;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=7ef1f4ccf86a8089834a589db6fe4ffa