sum data for quater

[fog2k]

Hi All,

I have many transaction data by date such sales.
I need to sum up sales for quarter in each product.

How I do?

[tamilselvan]

You can decode function.

[fog2k]

give me an example. pls.

[kmesser]

fog,

You didn't provide any structure info, so I guessed that you have a table named mytable with two columns of interest: transdate and transprice.

select
decode(to_char(transdate,'MON'),
'JAN','1st Quarter ',
'FEB','1st Quarter ',
'MAR','1st Quarter ',
'APR','2nd Quarter ',
'MAY','2nd Quarter ',
'JUN','2nd Quarter ',
'JUL','3rd Quarter ',
'AUG','3rd Quarter ',
'SEP','3rd Quarter ',
'OCT','4th Quarter ',
'NOV','4th Quarter ',
'DEC','th Quarter '
) ||TO_CHAR(TRANSDATE,'YYYY') transquarter,
sum(transprice)
from mytable group by
decode(to_char(transdate,'MON'),
'JAN','1st Quarter ',
'FEB','1st Quarter ',
'MAR','1st Quarter ',
'APR','2nd Quarter ',
'MAY','2nd Quarter ',
'JUN','2nd Quarter ',
'JUL','3rd Quarter ',
'AUG','3rd Quarter ',
'SEP','3rd Quarter ',
'OCT','4th Quarter ',
'NOV','4th Quarter ',
'DEC','th Quarter '
) ||TO_CHAR(TRANSDATE,'YYYY')
order by substr(transquarter,16,19) asc, transquarter asc
/

[shawish_sababa]

why not use the "group by" ans sum(_) is the select part of the statment

select sum(_)
from ___
where ___
grou by ___

[kmesser]

Shaw:

What would you group by? If you group by transdate, then the results are grouped by transdate rather than quarter, and the quarters would not be identified. If I'm not getting your point, please post a complete select statement that I can understand/test.

Thanks!

Here's my table:

SQL> desc mytable
Name Null? Type
----------------------------------------- -------- -----------
TRANSDATE DATE
TRANSPRICE NUMBER(6,2)

SQL> select * from mytable;

TRANSDATE TRANSPRICE
--------- ----------
02-JAN-01 10
03-JAN-01 10
04-JAN-01 10
02-JAN-00 10
02-JAN-00 10
02-JAN-00 10
02-JAN-00 10
02-JAN-01 10
02-JAN-01 10
02-JAN-01 10
02-JAN-01 10

TRANSDATE TRANSPRICE
--------- ----------
15-SEP-00 10
15-SEP-00 10
15-SEP-00 10
15-SEP-00 10

15 rows selected.


Then I run the query I posted earlier:

TRANSQUARTER SUM(TRANSPRICE)
---------------- ---------------
1st Quarter 2000 40
3rd Quarter 2000 40
1st Quarter 2001 70

As I said above, if I'm not getting your point, please correct me.

Thanks!

[jmodic]

Oracle provides format model 'Q' for date-to-cahr and char_to_date conversion that we could use to get a quarter the date belongs to.

SELECT TO_CHAR(transdate,'YYYY') year, TO_CHAR(transdate,'Q') quarter, SUM(sales)
FROM sales_table
GROUP BY TO_CHAR(transdate,'YYYY'), TO_CHAR(transdate,'Q');

So there is no need to use DECODE....

[kmesser]

thanks jmodic! That cleans this up a bunch.

[shawish_sababa]

Hi kmesser

thank you for the TO_CHAR(transdate,'Q') i was looking for this in the documentation and could not find it.

that is way i did not answer you kmesser

[fog2k]

thanks all,

According to kmesser data,
if I specified department to data
That means I need to get sum(transprice) by department
and quarter.