I have 2 tables
ARAMIS @ UKDB02P2>desc arm_sub_project
Name Null? Type
--------------------------
SUB_PROJECT_NUMBER NOT NULL NUMBER(7)
DATE_CREATED NOT NULL DATE
STATUS NOT NULL CHAR(1)
PROJECT NOT NULL NUMBER(7)
APPROVED CHAR(1)
ARAMIS @ UKDB02P2>desc arm_sub_project_budget_lin
Name Null? Type
--------------------------
BUDGET_LINE NOT NULL NUMBER
SUB_PROJECT_NUMBER NOT NULL NUMBER(7)
CATEGORY_AVAILABLE NOT NULL NUMBER
COMPANY_NUMBER NOT NULL NUMBER
and have now been asked to write a sql which would display the following:
MONTH/YEAR from table 1
SUB_PROJECT_NUMBER from table 2
MIN(COUNT(BUDGET_LINE)) from table 2
MAX(COUNT(BUDGET_LINE)) from table 2
AVG(COUNT(BUDGET_LINE)) from table 2
i.e to put in words, for each month/year the project manager wanst to see the total number of budget_lines, minimum number of budget_lines and maximum number of budget_lines for each sub_project_number
Thanks!
Ali
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Try this:
select DT, MIN(BL) OVER ( ORDER BY NULL), MAX(BL) OVER ( ORDER BY NULL),
AVG(BL) OVER ( ORDER BY NULL )
FROM(
select to_char(date_created,'MM/Y
from arm_sub_project a, arm_sub_project_budget_lin
where a.SUB_PROJECT_NUMBER = b.SUB_PROJECT_NUMBER
group by to_char(date_created,'MM/Y
/
Isn't a join missing here. Don't you need month also in the arm_sub_project_budget_lin
May be i am missing something?
A sample of the data will help to understand the problem better.
How about this?
select RptDate, min(LineCount), max(LineCount), avg(LineCount) from
(select to_char(a.date_created,'mm
from arm_sub_project a
, arm_sub_project_budget_lin
where a.SUB_PROJECT_NUMBER = b.SUB_PROJECT_NUMBER
group by to_char(a.date_created))
Business Accounts
Answer for Membership
by: Bigfam5Posted on 2005-02-01 at 04:24:31ID: 13191769
Select to_char(A.DATE_CREATED, 'MON-YYYY'), B.Sub_Project_Number,
Min(B.Budget_line), Max(B.Budget_Line), Avg(B.Budget_Line)
from arm_sub_project A, arm_sub_project_budget_lin
where A.Sub_Project_Number = B.Sub_Project_Number
group by to_char(A.DATE_CREATED, 'MON-YYYY'), B.Sub_Project_Number
/