SQL – I need help How to COUNT all sales by month within a year

I can’t figure out a way to COUNT al transactions by month each YEAR on a single query. I figured out a way but for one month at a time, there’s a lot of data (time consuming)

this is the table

i want to have something like this

thank you

Answer

here is how you can do it , using extract to extract month /year from your date column and group by it:

select 
  extract(year_month from Date) year_month
 , count(*) total_sales
from your table
group by extract(year_month from Date) 
order by extract(year_month from Date)