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
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi guys
This is exactly what I was looking for.
Thank you for your help.
Mike
This is exactly what I was looking for.
Thank you for your help.
Mike
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:
Open in new window