0

I have a table and column containing date (say in string). I need to get two columns such as countin(2020) and countin(2019) from the same date column such that date is like '2020%' or '2019%' and I want them as separate columns.

My query is kind of like this.

select C.pin_code, count(distinct(C.customer_code)) as 2020 
from table 
group by C.pin_code

My output is this

enter image description here

For me there should be another column beside 2020 called 2019 which give same data as 2020 in year 2019.

If I have under emphasized something, please let me know in the comments.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
RAM SHANKER G
  • 35
  • 4
  • (1) Tag your question with the database you are using. (2) What does your data look like? (3) What does your existing query have to do with year 2020? – Gordon Linoff Jun 13 '20 at 11:09

2 Answers2

0
Select
Count(Year(year_col)) as year,
Pin_code
From T
Group_by pin_code
Order by pin_code desc;

You can use pivot in case you need those values in year column

dsk
  • 1,863
  • 2
  • 10
  • 13
0

For me there should be another column beside 2020 called 2019 which give same data as 2020 in year 2019.

If your data has a date in it, then I would expect a query like this:

select C.pin_code,
       count(distinct case when year(C.date) = 2020 then C.customer_code end) as cnt_2020, 
       count(distinct case when year(C.date) = 2019 then C.customer_code end) as cnt_2019
from C 
group by C.pin_code;

Date/time functions are notoriously database dependent. But year() is pretty common and all databases have this functionality somehow.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Hii I get the error "Error while executing SQL query on database 'Salestable': no such function: year" I am using sqlite – RAM SHANKER G Jun 13 '20 at 14:55