CODEWITHSUNDEEP
mysqlmysql-5.6
user3369545
user3369545

Reputation: 421

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

Upvotes: 0

Views: 40

Answers (1)

Ergest Basha
Ergest Basha

Reputation: 8973

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

Upvotes: 1

Related Questions