Home » SQL & PL/SQL » SQL & PL/SQL » Generate month wise data using SQL (Sort data based on month) (Oracle DB 11g Release 11.2.0.2.0)
Generate month wise data using SQL (Sort data based on month) [message #683810] Mon, 22 February 2021 23:23 Go to next message
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 Go to previous message
Michel Cadot
Messages: 67842
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

Previous Topic: A full outer join query
Next Topic: call sql server procedure from oracle
Goto Forum:
  


Current Time: Sat May 08 21:14:51 CDT 2021