Solved

SQL, Bring in another column?

Posted on 2014-04-18
12
460 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 22

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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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 22

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 22

Accepted Solution

by:
Steve Wales earned 500 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 22

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

728 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