CODEWITHSUNDEEP
sqloracle-databasedatesum
adam
adam

Reputation: 13

Summing a column by month

being a bit of an Oracle SQL newby I have a small conundrum.

I want to sum a months worth of data from the column "WEIGHT". START_DATESTAMP is originally in this format '01/01/1900 00:00:00' and I've found that using 'trunc' removes the time component.

So, the query below returns the summed weight per day but I want to return the summed weight per month. Any ideas?

SELECT 
CODE, trunc(START_DATESTAMP), sum(WEIGHT) as TOTAL_WEIGHT

FROM TP.LOADING Tbl

where CODE like 'ALM'
and trunc(START_DATESTAMP) >= to_date('01/11/2014', 'DD/MM/YYYY')
and trunc(START_DATESTAMP) < to_date('01/12/2014', 'DD/MM/YYYY')

group by CODE, trunc(START_DATESTAMP)

ORDER BY CODE asc, trunc(START_DATESTAMP) asc

Upvotes: 1

Views: 77

Answers (2)

Glenn
Glenn

Reputation: 9170

Take a look at the doc for trunc formats. If you want to trunc by month:

group by CODE, trunc(START_DATESTAMP, 'MM')

Upvotes: 0

wwkudu
wwkudu

Reputation: 2796

You could try something like the following:

SELECT CODE, to_char(START_DATESTAMP, 'YYYY/MM') as MONTH, sum(WEIGHT) as TOTAL_WEIGHT
FROM LOADING Tbl
where CODE like 'ALM'
  and trunc(START_DATESTAMP) >= to_date('01/01/2014', 'DD/MM/YYYY')
  and trunc(START_DATESTAMP) < to_date('01/12/2014', 'DD/MM/YYYY')
group by CODE, to_char(START_DATESTAMP, 'YYYY/MM')
ORDER BY CODE asc, MONTH asc

adjusting your date checks accordingly. You don't say what you want to do for months in different years - but you can convert to a pure month string, or perhaps use the EXTRACT function for just a numeric month.

Upvotes: 1

Related Questions