-
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?
-
-
-
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
/
-
why not use the "group by" ans sum(_) is the select part of the statment
select sum(_)
from ___
where ___
grou by ___
shawish_sababa
shawish_sababa@hotmail.com
-
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!
-
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....
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
thanks jmodic! That cleans this up a bunch.
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|