Solved

SQL, Bring in another column?

Posted on 2014-04-18
12
456 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
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 76

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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 76

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

920 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now