Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL, Bring in another column?

Posted on 2014-04-18
12
Medium Priority
?
462 Views
Last Modified: 2014-04-18
Okay so I'm new to sql. Digging on some websites I've found some examples that match my situation.
SELECT EMP_NUM,SUM(ASSIGN_HOURS) AS SumOfAssign_Hours,SUM(ASSIGN_CHARGE) AS SumOfAssign_CHARGE
FROM ASSIGNMENT
GROUP BY EMP_NUM;

What if I have a column located on another table that I want to bring into this table query?

The above query works great for everything I need, HOWEVER, what if I want to bring in a column called EMP_LNAME for example from another table?

I understand that I probably have to use a join command somewhere, but to make it work without conflicting with my above queries is what I'm having issues with.

Any suggestions on how to bring in EMP_LNAME from another table that doesn't conflict with my script above?
0
Comment
Question by:Pancake_Effect
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40009449
Untested but should go like:

SELECT EMP_LNAME,SUM(ASSIGN_HOURS) AS SumOfAssign_Hours,SUM(ASSIGN_CHARGE) AS SumOfAssign_CHARGE
FROM ASSIGNMENT A JOIN ANOTHER_TABLE T ON T.EMP_NUM=A.EMP_NUM
GROUP BY EMP_LNAME;
0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 40009471
Everything worked, but it's missing the EMP_NUM field during the select. When I add it back in like so:

SELECT EMP_NUM, EMP_LNAME,SUM(ASSIGN_HOURS) AS SumOfAssign_Hours,SUM(ASSIGN_CHARGE) AS SumOfAssign_CHARGE

it fails to work, however in your format it works. But I really need the EMP_NUM column left in it some how.
0
 
LVL 23

Expert Comment

by:Steve Wales
ID: 40009495
If EMP_NUM is in both tables, you need to qualify it as to which table it belongs to, otherwise it is an ambiguous reference.

SELECT A.EMP_NUM, EMP_LNAME,SUM(ASSIGN_HOURS) AS SumOfAssign_Hours,SUM(ASSIGN_CHARGE) AS SumOfAssign_CHARGE
FROM ASSIGNMENT A JOIN ANOTHER_TABLE T ON T.EMP_NUM=A.EMP_NUM
GROUP BY EMP_LNAME;

"A" and "T" are aliases for the table names.

You could also change a.emp_num to assignment.emp_num.

The other columns, since they are unique (only referenced each once between all the tables in the query) don't need qualifying (but I always find it good practice to do so when using a join so that you know where everything is coming from in a complex query that you have to come back to at a later date.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 4

Author Comment

by:Pancake_Effect
ID: 40009514
Okay so here is more information (this includes all the table names it's referencing) and by using your guide, here it is below. It's still not working, but from a logical flow, it's what I'm shooting to make it work. I don't know if you can pick out the broken parts?

EMP_NUM is referenced in both tables, and I am getting a ambiguous reference. How do I "qualify" it as not to receive the error? I need it grouped by EMP_NUM which I changed below.

I also changed the alisis to match better, I'm not sure what the whole "ON E.EMP_NUM=A.EMP_NUM" part entails though exactly.

SELECT E.EMP_NUM, EMP_LNAME,SUM(ASSIGN_HOURS) AS SumOfAssign_Hours,SUM(ASSIGN_CHARGE) AS SumOfAssign_CHARGE
FROM ASSIGNMENT A 
JOIN EMPLOYEE E 
ON E.EMP_NUM=A.EMP_NUM
GROUP BY EMP_NUM;

Open in new window

0
 
LVL 32

Expert Comment

by:awking00
ID: 40009517
Steve,
I think your query needs to group by all of the non-aggregated fields -
group by a.emp_no, t.emp_lname
0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 40009557
Not sure what that means, any idea awking?
0
 
LVL 23

Expert Comment

by:Steve Wales
ID: 40009614
Oops!  You're correct awking00.  Forgot to amend the group by clause:

SELECT E.EMP_NUM, EMP_LNAME,SUM(ASSIGN_HOURS) AS SumOfAssign_Hours,SUM(ASSIGN_CHARGE) AS SumOfAssign_CHARGE
FROM ASSIGNMENT A 
JOIN EMPLOYEE E 
ON E.EMP_NUM=A.EMP_NUM
GROUP BY E.EMP_NUM, EMP_LNAME;

Open in new window


Thanks for the pickup!
0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 40009626
Working better!

But why does it exactly need both the emp_num and emp_lname?

Is there any way to group by just emp_num?

When I take emp_lname out of the script it then states "not GROUP BY expression" error

Main reasoning was because it seems to me to be more simple to sort by emp_num
0
 
LVL 23

Accepted Solution

by:
Steve Wales earned 2000 total points
ID: 40009634
No way to remove it - that's the way aggregate expressions work.

You don't have to ORDER BY the name, but you do have to GROUP every non aggregate column in the query.

So you could turn it into this:

SELECT E.EMP_NUM, EMP_LNAME,SUM(ASSIGN_HOURS) AS SumOfAssign_Hours,SUM(ASSIGN_CHARGE) AS SumOfAssign_CHARGE
FROM ASSIGNMENT A 
JOIN EMPLOYEE E 
ON E.EMP_NUM=A.EMP_NUM
GROUP BY E.EMP_NUM, EMP_LNAME
ORDER BY E.EMP_NUM;

Open in new window

0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 40009642
Works beautifully now!

This has helped a lot, I think I understand all the commands you defined and did.

However the only command I'm wondering about what it actually does is:

ON E.EMP_NUM=A.EMP_NUM

What does that actually define or do? This is my understanding below

FROM ASSIGNMENT A     (* This takes/defines table A)
JOIN EMPLOYEE E            (*This takes/defines table B)
ON E.EMP_NUM=A.EMP_NUM  (*Does this mean link them via their primary/foreign key? Or how they are linked?)

Again everything is working now, this is just a out of my own curiosity.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40009737
>>Or how they are linked?)

Correct.  That specifies how the two tables are 'joined'.
0
 
LVL 23

Expert Comment

by:Steve Wales
ID: 40009775
Lots of different types of JOIN's too (without trying to overwhelm you).

Here's a couple of links (Oracle Docs for 10g, couldn't find the 11g equivalent) and the Wikipedia entry (which should always be taken with a grain of salt because of how easily it can be edited, but is still a decent article to explain the concepts)

http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries006.htm
http://en.wikipedia.org/wiki/Join_%28SQL%29
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question