Link to home
Start Free TrialLog in
Avatar of mikeysmailbox1
mikeysmailbox1

asked on

Oracle SQL get counts from single table based on date and column

Hi I need help with the following.

I have a table that has the following columns

TABLE     APPLICATION   GROUP   JOBNAME     DATE      
-------   -----------   -----   --------    -------  
AAA       MYAPP1        AAZ     Command1    20140401  
BBB       MYAPP1        DAZ     Command1    20140401  
BBB       MYAPP1        DAZ     Command2    20140402  
AAA       MYAPP1        AAZ     Command1    20140403  
CCC       AZZCX         AAZ     Command1    20140401  
AAA       MYAPP         AAB     Command3    20140405  
CCC       MYAPP         AAD     Command5    20140401  
DDD       MYAPP         AAZ     Command1    20140501  
AAA       MYAPP         AAB     Command1    20140401  
                                                     
                                                     
This is the output I need.                                                      

                                                                 
APPLICATION     GROUP     JOBCOUNT                    
-----------     ------    --------                    
MYAPP1    AAZ       1                          
                  DAZ       2                          
AZZCX       AAZ       1                          
MYAPP     AAB       2                          
                 AAD       1                          
                  AAZ       1                          


I need to search for the Application then the Group and count if the Jobname exist at least once.
and if the date is between X and Y.

Not sure how this would work.

Thanks,

Mike
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

You'll need to add the dates between logic.

I had to change some of your column names so they weren't reserved words but take a look at this and see if this is what you are after:
drop table tab1 purge;
create table tab1(
myTABLE varchar2(3),  
APPLICATION varchar2(6),  
myGROUP varchar2(3),  
JOBNAME varchar2(8),
myDATE date
);


insert into tab1 values('AAA','MYAPP1','AAZ','Command1',to_date('20140401','YYYYMMDD'));
insert into tab1 values('BBB','MYAPP1','DAZ','Command1',to_date('20140401','YYYYMMDD'));
insert into tab1 values('BBB','MYAPP1','DAZ','Command2',to_date('20140402','YYYYMMDD'));
insert into tab1 values('AAA','MYAPP1','AAZ','Command1',to_date('20140403','YYYYMMDD'));
insert into tab1 values('CCC','AZZCX','AAZ','Command1',to_date('20140401','YYYYMMDD'));
insert into tab1 values('AAA','MYAPP','AAB','Command3',to_date('20140405','YYYYMMDD'));
insert into tab1 values('CCC','MYAPP','AAD','Command5',to_date('20140401','YYYYMMDD'));
insert into tab1 values('DDD','MYAPP','AAZ','Command1',to_date('20140501','YYYYMMDD'));
insert into tab1 values('AAA','MYAPP','AAB','Command1',to_date('20140401','YYYYMMDD'));
commit;
                                                     
                                                     
select
	case when rn=1 then application end,
	mygroup,
	mycount
from
(
	select application, mygroup, count(*) mycount,
		row_number() over(partition by application order by application, mygroup) rn
	from tab1
	group by application, mygroup
)
/

Open in new window

Avatar of mikeysmailbox1

ASKER

Hi slightwv,

This is very close. I need the JOBNAME count to count only once even though they have different dates.

Thanks for the help.

Mike
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi guys

This is exactly what I was looking for.

Thank you for your help.

Mike