How can I do a yearly report grouped by month?
I am using the开发者_JS百科 below query to list the number of transactions by month. Does anyone know how can I list by year too. This means that the query returns all my transactions for the whole year except the current month.
That is if today it is the 29th August 2011 I need a yearly report grouped by month till the month of July (since august is not complete)
select to_char(date,'MONTH YYYY'), sum(number_of_transactions)
from header
group by date
order by date
select to_char(trunc(date,'yyyy'),'YYYY') as year, sum(number_of_transactions)
from header
where date < trunc(sysdate, 'mm')
group by trunc(date,'yyyy')
order by year
Is this what you need?
SELECT TO_CHAR(date, 'YYYY'), SUM(number_of_transactions)
FROM header
GROUP BY TO_CHAR(date, 'YYYY')
精彩评论