| Analytical functions [message #684045] |
Mon, 29 March 2021 05:18  |
 |
jay_yan
Messages: 2
Registered: March 2021
|
Junior Member |
|
|
Hello,
i have below 2 tables
trans_table
Id, Mode , Date_created, sales
1 Phone 2021-01-01 1000
2 modem 2021-01-12 2500
3 Internet 2021-02-01 3500
4 phone 2021-03-01 3600
5 Phone 2021-03-01 2300
Cal_table
Cal_id, year_month, month_st_date, month_end_date
1 202101 2021-01-01 2021-01-31
2 202102 2021-02-01 2021-02-28
3 202103 2021-03-01 2021-03-31
I need query for monthly sales by mode and the previous month's sales.
|
|
|
|
| Re: Analytical functions [message #684046 is a reply to message #684045] |
Mon, 29 March 2021 05:26  |
 |
Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Note that we don't do homework but we may help you to do them, post what you already tried, what is the problem with it and where you are stuck.
|
|
|
|
|
|
|
|
| Re: Analytical functions [message #684051 is a reply to message #684049] |
Mon, 29 March 2021 10:30 |
|
Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
jay_yan wrote on Mon, 29 March 2021 15:03Thank you for your response.
I was trying below query for monthly sales by mode, but iam not sure how far this is correct.
select a.mode, b.year_month,sum(sales) over (Partition by a.mode, month (a.date_created) as sum(sales)) from trans_table a,
cal_table b
where a.date_created between b.month_st_date and b.month_end_date
I should only write one sentence per post as it seems some people are unable to read one, the fist or the last one.
So I repeat, please read and apply all sentences:
Michel Cadot wrote on Mon, 29 March 2021 12:26
Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Note that we don't do homework but we may help you to do them, post what you already tried, what is the problem with it and where you are stuck.
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
