Home » SQL & PL/SQL » SQL & PL/SQL » Analytical functions
Analytical functions [message #684045] Mon, 29 March 2021 05:18 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68043
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 #684049 is a reply to message #684046] Mon, 29 March 2021 08:03 Go to previous messageGo to next message
jay_yan
Messages: 2
Registered: March 2021
Junior Member
Thank 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
Re: Analytical functions [message #684050 is a reply to message #684049] Mon, 29 March 2021 08:15 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3104
Registered: January 2010
Location: Connecticut, USA
Senior Member
You need to GROUP BY and apply analytic LAG.

SY.
Re: Analytical functions [message #684051 is a reply to message #684049] Mon, 29 March 2021 10:30 Go to previous message
Michel Cadot
Messages: 68043
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

jay_yan wrote on Mon, 29 March 2021 15:03
Thank 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.
Previous Topic: sort with duplicate records
Next Topic: group by common string
Goto Forum:
  


Current Time: Thu Dec 02 15:56:10 CST 2021