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

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.
chaatnaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaatnaAuthor Commented:
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
RiteshShahCommented:
sorry didn't get you exactly..... month wise pivot is given in both of the above links.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

chaatnaAuthor Commented:
Hi Ritesh,

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

Thanks,
Chandru
0
RiteshShahCommented:
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
slightwv (䄆 Netminder) Commented:
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
chaatnaAuthor Commented:
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
slightwv (䄆 Netminder) Commented:
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
chaatnaAuthor Commented:
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
slightwv (䄆 Netminder) Commented:
>>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
chaatnaAuthor Commented:
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
slightwv (䄆 Netminder) Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chaatnaAuthor Commented:
Thanks slightwv.

Issue is resolved.
0
chaatnaAuthor Commented:
Nice one, thanks a lot!
0
gotetioracleCommented:
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
slightwv (䄆 Netminder) Commented:
gotetioracle,

If you need help with a problem you are having, please ask a new question.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.