0

I have a table with two columns, date and number of pieces, just like this:

pieces          Date

100             2022-01-01
200             2022-02-01
300             2022-03-01

and so on.

I want to sum the number of pieces in the way that I increment the newest month, something like this:

january    - 100 pieces
february   - 300 pieces
march      - 600 pieces

How would I do that?

What I've tried so far is make individual selects with one sum up to a point and then union with another, up to another point, but it seems counter productive.

select
  sum(pieces)
  , 'january' as month
from
  table
where
  date <= '2022-01-31'
union
select
  sum(pieces)
  , 'february' as month
from
  table
where
  date <= '2022-02-28'
union
select
  sum(pieces)
  , 'march' as month
from
  table
where
  date <= '2022-03-31'
Matheus Esteves
  • 1
  • You want running sum query - a very common topic. – June7 Nov 24 '22 at 18:55
  • One more idea - maybe extract the month from date and then group based on it ? `SELECT date_format(date '1970-01-01', 'MMMM');` Refer to link https://docs.databricks.com/sql/language-manual/functions/date_format.html – rainingdistros Nov 25 '22 at 06:40
  • in which programming language you are trying to achieve...if you are good with "r" then use "cumsum" function to do and will get you the desired output. – sai saran Nov 25 '22 at 08:23

1 Answers1

0

say your dataframe is 'df'

step 1: create new column as "month" by 'month<-months(as.Date("Date"))" #new column will be created

step 2: use "group by" or "cumsum"

sai saran
  • 737
  • 9
  • 32