sum data for quater

# Thread: sum data for quater

1. Junior Member
Join Date
Feb 2001
Posts
30
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?

2. Moderator
Join Date
May 2000
Location
ATLANTA, GA, USA
Posts
3,135
You can decode function.

3. Junior Member
Join Date
Feb 2001
Posts
30
give me an example. pls.

4. Senior Member
Join Date
Aug 2000
Posts
462
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
/

5. Join Date
Jul 2000
Posts
243
why not use the "group by" ans sum(_) is the select part of the statment

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

6. Senior Member
Join Date
Aug 2000
Posts
462
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!

7. Super Moderator
Join Date
Dec 2000
Location
Ljubljana, Slovenia
Posts
4,439
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....

8. Senior Member
Join Date
Aug 2000
Posts
462
thanks jmodic! That cleans this up a bunch.

9. Join Date
Jul 2000
Posts
243
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

10. Junior Member
Join Date
Feb 2001
Posts
30
thanks all,

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•