I'm attempting to create a new view

Of an existing table. The existing table is composed of 3 columns, ExpertiseID, Description, Hourlyrate. The new table is similar to the old table except the Hourlyrate needs to increase by 20. There are 10 rows per column, ExpertiseID's are integer values 1-10, Description relates the the ExpertiseID, and the rates are random $ values that need to be increased each by $20.

ExpertiseID --> Description

What I am entering into the query

CREATE VIEW NewTable AS
SELECT "ExpertiseID" AS "ExpertiseID",
"Description" AS "Description"
sum(("Hourlyrate"),20) AS "Hourlyrate"<---- Where I'm F'n up
FROM ExistingTable

Am I going about this right or supposed to be using an UPDATE command?

 

I may have to be employing a correlated subquery.

 

I don't think it's Oracle specific. SUM is a function requiring GROUP BY. An equivalent MS SQL would be

SELECT "ExpertiseID" AS "ExpertiseID",
"Description" AS "Description"
(Hourlyrate+20) AS "Hourlyrate"
FROM ExistingTable

 

Yeah I just figured out the ("Hourlyrate"+20) AS "Hourlyrate" hopefully this will work now.

 

Yep, worked...thanks Anachostic

 

Ugh hopefully Anachostic or someone else could see this that could help

I have two tables
SKILLS(SkillsID, ConsultantID, ExpertiseID) SKILLSID = PK And other values are Foreign Keys
EXPERTISE(ExpertiseID, Description) ExpertiseID = PK

SkillsID is an Integer 1-22
ExpertiseID is an Integer 1-10
Description is just text
ConsultantID is an integer 1-13

I'm supposed to display which areas of expertise has 2 or less consultants and the description of the expertise area


I've done this

SELECT COUNT (*) AS "Number_Of_Consultants", "ExpertiseID"
FROM SKILLS
GROUP BY "ExpertiseID"
HAVING COUNT(*) <= 2
ORDER BY COUNT (*)

Which displays the Number of Consultants and the ExpertiseIDs... now I need to link/replace the ExpertiseIDs to the Description

I'm certain I don't need ConsultantID and maybe I have to join the tables.

 

SELECT COUNT ("ExpertiseID") AS "Number_Of_Consultants", "ExpertiseID"
FROM SKILLS
GROUP BY "ExpertiseID"
HAVING COUNT("ExpertiseID") <= 2
ORDER BY COUNT ("ExpertiseID")

 

Definitely need a join.

select count(distinct s.ConsultantID) NumberOfConsultants,e.ExpertiseID
from Skills s
join Expertise e on e.ExpertiseID=s.ExpertiseID
group by e.ExpertiseID
having count(s.ConsultantID)<=2
order by 1 --Not sure if oracle supports column ordinal index for sorting

I'm waiting for the upcoming question that will result in the answer: "You need to use a derived table."

Edit: ...which may be this this one. Will the Skills table have repeating values of ConsultantID?
Edit2 (the distinct keyword may help)

 

I entered this

SELECT COUNT ("SKILLS.ConsultantID")AS "NumberOfConsultants", "EXPERTISE.ExpertiseID"
FROM SKILLS
JOIN EXPERTISE
ON "EXPERTISE.ExpertiseID" = "SKILLS.ExpertiseID"
GROUP BY "EXPERTISE.ExpertiseID"
HAVING COUNT ("SKILLS.ConsultantID") <= 2
ORDER BY 1

And got this error

>[Error] Script lines: 1-7 --------------------------
ORA-00904: "SKILLS.ExpertiseID": invalid identifier 

 

Quote the table and the field independantly: "Skills"."ExpereinceID" Or not at all if there's no spaces in the names. You can also alias the table names like I did so you can use shorter qualifiers.

 

The resulting table is

NumberOfConsultants ExpertiseID
---------------------- --------------
1 5
1 6
1 4
2 9
2 8
2 10

However, I need the description of the Expertise
When I enter this into the query

SELECT COUNT ( DISTINCT "SKILLS"."ConsultantID")AS "NumberOfConsultants", "EXPERTISE"."ExpertiseID", "EXPERTISE"."Description"
FROM SKILLS
JOIN EXPERTISE
ON "EXPERTISE"."ExpertiseID" = "SKILLS"."ExpertiseID"
GROUP BY "EXPERTISE"."ExpertiseID"
HAVING COUNT ("SKILLS"."ConsultantID") <= 2
ORDER BY 1

I receive this

>[Error] Script lines: 1-8 --------------------------
ORA-00979: not a GROUP BY expression 

 

EDIT

some minor changes...
SELECT COUNT ( DISTINCT "SKILLS"."ConsultantID")AS "NumberOfConsultants", "EXPERTISE"."Description"
FROM SKILLS
JOIN EXPERTISE
ON "EXPERTISE"."ExpertiseID" = "SKILLS"."ExpertiseID"
GROUP BY "EXPERTISE"."Description"
HAVING COUNT ("SKILLS"."ConsultantID") <= 2
ORDER BY 1


NumberOfConsultants Description
---------------------- ----------------
1 COBOL
1 C++
1 Java
2 GPSS
2 Crystal Analysis
2 Cold Fusion

That worked perfectly... thanks again for the help

 

Put Experience.Description in your GROUP BY clause. Any field that is not in an aggregate function like SUM, COUNT, MIN, etc has to be in the GROUP BY clause.

That's pretty basic stuff. You must be getting frustrated.

 

Yeah, it doesn't help this is an online course, might have been better to take it in person. This is the last project b4 tomorrow's final.

 

I know MySQL, but not MS

The search queries are quite different

 

It's only a little bit of syntax... it doesn't take long to pick up another as long as you know sql...

 

Yeah that's what I've heard

I've just never used (or needed to use) and other programs

 

Yeah, I don't quote, and always use aliases for table names so I can use shorter qualifiers....

 

I've got 6 years of Oracle SQL, and 3 months of MS SQL... It's going to be interesting at my new job where they use MS SQL. Gonna take a bit to get back up to speed using MS SQL.

 

Not really, unless you're of the mind that everything should be done with a cursor. You'll have to work a little harder with the loss of some Oracle shortcuts like natural joins but you'll get a feature I missed greatly in Oracle: UPDATE FROM and DELETE FROM.

But yeah, if you stick to ANSI SQL, you can go anywhere.

 

Something i've been wanting to learn for a long time.