?
Solved

How can query to retrieve month wise results for a given year

Posted on 2010-04-07
16
Medium Priority
?
8,618 Views
Last Modified: 2013-12-19
Hi,

I have a requirement of displaying the count results month wise if I select a particular year as per following format:
Jan   Feb  Mar  Apr .................Dec  Total
----------------------------------------------------
20    23   45   56   ................12      xxxx

How to split the total count value into month wise like above? Plz suggest.
0
Comment
Question by:chaatna
  • 7
  • 5
  • 3
  • +1
16 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 29997721
0
 

Author Comment

by:chaatna
ID: 30000679
Hi,

Have you any query specific to display results month wise for a given year in the above format which is helpful to me.

Thanks,
Chandru
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 30000885
sorry didn't get you exactly..... month wise pivot is given in both of the above links.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 

Author Comment

by:chaatna
ID: 30002437
Hi Ritesh,

Are you from India or US? If yes, Could you provide your number, please?

Thanks,
Chandru
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 30003260
Sorry, It is against the rule of EE. however if you would like to contact any EE member, you can go to their profile page and contact them personally

0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 30016601
There are a lot of examples in the askTom link above.  Here's what I think you need (It was tested with 10.2.0.3).


drop table tab1 purge;
create table tab1(col1 date);

insert into tab1 values(to_date('01/01/2010','MM/DD/YYYY'));
insert into tab1 values(to_date('01/01/2009','MM/DD/YYYY'));
insert into tab1 values(to_date('03/01/2010','MM/DD/YYYY'));
insert into tab1 values(to_date('04/01/2010','MM/DD/YYYY'));
insert into tab1 values(to_date('08/01/2010','MM/DD/YYYY'));
commit;


with myTab as (
select 
	sum(case to_char(col1,'MM') when '01' then 1 else 0 end) jan,
	sum(case to_char(col1,'MM') when '02' then 1 else 0 end) feb,
	sum(case to_char(col1,'MM') when '03' then 1 else 0 end) mar,
	sum(case to_char(col1,'MM') when '04' then 1 else 0 end) apr,
	sum(case to_char(col1,'MM') when '05' then 1 else 0 end) may,
	sum(case to_char(col1,'MM') when '06' then 1 else 0 end) jun,
	sum(case to_char(col1,'MM') when '07' then 1 else 0 end) jul,
	sum(case to_char(col1,'MM') when '08' then 1 else 0 end) aug,
	sum(case to_char(col1,'MM') when '09' then 1 else 0 end) sep,
	sum(case to_char(col1,'MM') when '10' then 1 else 0 end) oct,
	sum(case to_char(col1,'MM') when '11' then 1 else 0 end) nov,
	sum(case to_char(col1,'MM') when '12' then 1 else 0 end) dec
from tab1 where to_char(col1,'YYYY') = '2010')
select
	jan,feb,mar,apr,may,jun,jul,aug,sep,oct,nov,dec,
	jan+feb+mar+apr+may+jun+jul+aug+sep+oct+nov+dec total
from myTab;

Open in new window

0
 

Author Comment

by:chaatna
ID: 30027366
Hi,

I have the query which retrieves the records month wise in a year. But it is displaying the records for which month they exist. But I want 0 count in which month they are not present. Plz find the query:

SELECT TO_CHAR (TRUNC (crte_dttm, 'mm'), 'Month'),cnsmr_shrt_cd_id,  COUNT(*)
FROM message
WHERE EXTRACT(YEAR FROM CRTE_DTTM) = 2008
AND CNSMR_SHRT_CD_ID = 1
GROUP BY TRUNC (crte_dttm, 'mm'),cnsmr_shrt_cd_id
ORDER BY TRUNC (crte_dttm, 'mm')

Could anybody alter my query to display the results  as of my requirement like

Month  cnsmr_shrt_cd  count
Jan          341                  0
Feb          341                  1
Mar         341                   0
Apr          341                  12
May          341                  
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 30030471
You can use some union statements to get this but before we go down that path do you want the results as rows or columns?

If columns, my query returns 0 counts.
0
 

Author Comment

by:chaatna
ID: 30079796
Hi,

I can't drop table. Can't we have any alternative for this? Atleast for rowwise as what i gave above.
Thanks,
Chandru
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 30111388
>>I can't drop table

I'm not asking you to drop the table.  I provided a stand-alone test-case showing table structure and sample data to explain my select statement.

It is intended you you to take that example and modify the select to go against your tables.

Take my select statement replace my table name (tab1) with your table name and my date column (col1) with your date column.
0
 

Author Comment

by:chaatna
ID: 30620173
Hi,

I am getting error message DEC: invalid identifier in TOAD with the following query:
SELECT  
        SUM(CASE TO_CHAR(crte_dttm,'MM') WHEN '01' THEN 1 ELSE 0 END) jan,
        SUM(CASE TO_CHAR(crte_dttm,'MM') WHEN '02' THEN 1 ELSE 0 END) feb,
        SUM(CASE TO_CHAR(crte_dttm,'MM') WHEN '03' THEN 1 ELSE 0 END) mar,
        SUM(CASE TO_CHAR(crte_dttm,'MM') WHEN '04' THEN 1 ELSE 0 END) apr,
        SUM(CASE TO_CHAR(crte_dttm,'MM') WHEN '05' THEN 1 ELSE 0 END) may,
        SUM(CASE TO_CHAR(crte_dttm,'MM') WHEN '06' THEN 1 ELSE 0 END) jun,
        SUM(CASE TO_CHAR(crte_dttm,'MM') WHEN '07' THEN 1 ELSE 0 END) jul,
        SUM(CASE TO_CHAR(crte_dttm,'MM') WHEN '08' THEN 1 ELSE 0 END) aug,
        SUM(CASE TO_CHAR(crte_dttm,'MM') WHEN '09' THEN 1 ELSE 0 END) sep,
        SUM(CASE TO_CHAR(crte_dttm,'MM') WHEN '10' THEN 1 ELSE 0 END) oct,
        SUM(CASE TO_CHAR(crte_dttm,'MM') WHEN '11' THEN 1 ELSE 0 END) nov,
        SUM(CASE TO_CHAR(crte_dttm,'MM') WHEN '12' THEN 1 ELSE 0 END) DEC,
            jan+feb+mar+apr+may+jun+jul+aug+sep+oct+nov+DEC AS total
 FROM MESSAGE
     WHERE  TO_CHAR(crte_dttm,'YYYY') = '2010'
       AND CNSMR_SHRT_CD_ID = 341

But it is working fine if I replace the line "jan+feb+mar+apr+may+jun+jul+aug+sep+oct+nov+DEC AS total" with "count (*) as total".

Could I know what may be the reason?
Thanks,
Chandru
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 375 total points
ID: 30627687
In Oracle you can't use a column alias in the same select that it was created.

You either need to follow what I posted and use a inline view or copy all the sums and add them together like:

...
SUM(CASE TO_CHAR(crte_dttm,'MM') WHEN '11' THEN 1 ELSE 0 END) nov,
SUM(CASE TO_CHAR(crte_dttm,'MM') WHEN '12' THEN 1 ELSE 0 END) DEC,
            SUM(CASE TO_CHAR(crte_dttm,'MM') WHEN '01' THEN 1 ELSE 0 END)  +  SUM(CASE TO_CHAR(crte_dttm,'MM') WHEN '02' THEN 1 ELSE 0 END) + ...
as total
0
 

Author Comment

by:chaatna
ID: 31667566
Thanks slightwv.

Issue is resolved.
0
 

Author Closing Comment

by:chaatna
ID: 31667886
Nice one, thanks a lot!
0
 

Expert Comment

by:gotetioracle
ID: 39050371
I need day wise total for selected user transaction, total for all transactions how to imake query in oracle please find foloowing attachment,i need result same like that
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39051079
gotetioracle,

If you need help with a problem you are having, please ask a new question.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question