| Show all months between 2 months in a group by query [message #657750] |
Mon, 21 November 2016 02:18  |
 |
avni
Messages: 4
Registered: September 2016
|
Junior Member |
|
|
I have a query that will display month-year , region, country, customer and the values like sum(order), sum(invoice) .
ex:
SELECT month-year ,
region,
country,
customer,
SUM(order) ord,
SUM(invoice)inv,
from A,
B
where p_date BETWEEN :FROM_DATE AND :TO_DATE
GROUP BY month-year ,
region,
country,
customer ;
User enters :FROM_DATE =01-sep-2016 and :TO_DATE = 31-mar-2017
and the result is as below :
month-year region country customer ord inv
Nov-16 Africa Morocco xxx 4000 1234
Dec-16 Africa Morocoo abc 3500 1115
But I am looking for an output like below:
month-year region country customer ord inv
Sep-16 Africa Morocco xxx null null
Oct-16 Africa Morocoo xxx null null
Nov-16 Africa Morocco xxx 4000 1234
Dec-16 Africa Morocoo xxx null null
Jan-17 Africa Morocco xxx null null
Feb-17 Africa Morocoo xxx null null
Sep-16 Africa Morocco abc null null
Oct-16 Africa Morocoo abc null null
Nov-16 Africa Morocco abc null null
Dec-16 Africa Morocoo abc 3500 1115
Jan-17 Africa Morocco abc null null
Feb-17 Africa Morocoo abc null null
[/TABLE]
Which is the best and most efficient way of doing this?
Please help..
|
|
|
|
| Re: Show all months between 2 months in a group by query [message #657751 is a reply to message #657750] |
Mon, 21 November 2016 03:09  |
 |
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
From your previous topic:
Michel Cadot wrote on Thu, 22 September 2016 13:26
Please read 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.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Littlefoot wrote on Thu, 22 September 2016 19:58...
I applied [code] tags so that you could see that - even though it might look OK in Forms, it looks horrible when posted on the Forum. I'd suggest you to use code formatter, such as Instant SQL Formatter; bookmark it.
...
Your answer:
avni wrote on Thu, 22 September 2016 20:15Thank you for the suggestion. Will follow in the future .
But you didn't do it, did you?
In addition:
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.
You have to outer join your table with a calendar.
Example with the standard EMP table:
SQL> accept from_date date format 'DD/MM/YYYY' prompt 'From date? '
From date? 01/12/1980
SQL> accept to_date date format 'DD/MM/YYYY' prompt 'To date? '
To date? 01/06/1982
SQL> with
2 cal as (
3 select add_months(trunc(to_date('&&from_date','DD/MM/YYYY'),'MONTH'),level-1) mnth
4 from dual
5 connect by level <= months_between('&&to_date','&&from_date')+1
6 )
7 select to_char(mnth,'YYYY-MM') month, count(hiredate) nb_emp
8 from cal left outer join emp on trunc(hiredate,'MONTH') = mnth
9 group by mnth
10 order by mnth
11 /
MONTH NB_EMP
------- ----------
1980-12 1
1981-01 0
1981-02 2
1981-03 0
1981-04 1
1981-05 1
1981-06 1
1981-07 0
1981-08 0
1981-09 2
1981-10 0
1981-11 1
1981-12 2
1982-01 1
1982-02 0
1982-03 0
1982-04 0
1982-05 0
1982-06 0
19 rows selected.[Updated on: Mon, 21 November 2016 03:13] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
