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
Oracle Database

Avatar of undefined
Last Comment
mikeysmailbox1

8/22/2022 - Mon
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

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
PortletPaul

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
mikeysmailbox1

ASKER
Hi guys

This is exactly what I was looking for.

Thank you for your help.

Mike
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes