Generate month wise data using SQL (Sort data based on month) [message #683810] |
Mon, 22 February 2021 23:23 |
|
chauhanrahul780@yahoo.com
Messages: 1 Registered: February 2021
|
Junior Member |
|
|
Hi Friends,
I have a requirement to generate month-wise sales data for all the customers present in a table. If there is no data for a particular month the value should be printed as 0.
Sample data and expected output: Attached (sample_data.txt)
January to December should repeat for every customer and the month for which there is no sales, total_amount column should display 0. (total_amount is the sum of all the figures for a particular month).
Any help to achieve this is highly appreciated. Thanks
|
|
|
Re: Generate month wise data using SQL (Sort data based on month) [message #683813 is a reply to message #683810] |
Tue, 23 February 2021 00:17 |
|
Michel Cadot
Messages: 68665 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.
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 custom "calendar" query, something like:
SQL> select to_char(add_months(trunc(sysdate,'YEAR'),level-1),
2 'MONTH','NLS_DATE_LANGUAGE=AMERICAN') month,
3 level month_nb
4 from dual
5 connect by level <= 12
6 /
MONTH MONTH_NB
--------- ----------
JANUARY 1
FEBRUARY 2
MARCH 3
APRIL 4
MAY 5
JUNE 6
JULY 7
AUGUST 8
SEPTEMBER 9
OCTOBER 10
NOVEMBER 11
DECEMBER 12
12 rows selected.
SQL> break on deptno dup skip 1
SQL> with
2 cal as (
3 select to_char(add_months(trunc(sysdate,'YEAR'),level-1),
4 'MONTH','NLS_DATE_LANGUAGE=AMERICAN') month,
5 level month_nb
6 from dual
7 connect by level <= 12
8 )
9 select e.deptno, c.month, count(e.hiredate) nb_hired_in_month
10 from cal c left outer join emp e partition by (deptno)
11 on to_char(e.hiredate, 'MONTH','NLS_DATE_LANGUAGE=AMERICAN') = c.month
12 group by e.deptno, c.month, c.month_nb
13 order by e.deptno, c.month_nb
14 /
DEPTNO MONTH NB_HIRED_IN_MONTH
---------- --------- -----------------
10 JANUARY 1
10 FEBRUARY 0
10 MARCH 0
10 APRIL 0
10 MAY 0
10 JUNE 1
10 JULY 0
10 AUGUST 0
10 SEPTEMBER 0
10 OCTOBER 0
10 NOVEMBER 1
10 DECEMBER 0
20 JANUARY 0
20 FEBRUARY 0
20 MARCH 0
20 APRIL 2
20 MAY 1
20 JUNE 0
20 JULY 0
20 AUGUST 0
20 SEPTEMBER 0
20 OCTOBER 0
20 NOVEMBER 0
20 DECEMBER 2
30 JANUARY 0
30 FEBRUARY 2
30 MARCH 0
30 APRIL 0
30 MAY 1
30 JUNE 0
30 JULY 0
30 AUGUST 0
30 SEPTEMBER 2
30 OCTOBER 0
30 NOVEMBER 0
30 DECEMBER 1
36 rows selected.
[Updated on: Tue, 23 February 2021 00:27] Report message to a moderator
|
|
|