Home » SQL & PL/SQL » SQL & PL/SQL » simple query
simple query [message #278659] Mon, 05 November 2007 05:31 Go to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
I have a table.It has many rows.
I want to add certain rows .
For example
name     empno  sal  id   month
a         12    12   02    03
a         11    23   03    04
b         10    20   05    05

I want to add empno,sal of all the three rows so that result is

name empno sal
a 33 55
Re: simple query [message #278660 is a reply to message #278659] Mon, 05 November 2007 05:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, if you can tell us how to identify the rows that need to be added together, we can tell you how to sum them.
Re: simple query [message #278668 is a reply to message #278659] Mon, 05 November 2007 06:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use a where clause to get these rows then use sum to sum up the values.

Regards
Michel

Re: simple query [message #278716 is a reply to message #278659] Mon, 05 November 2007 08:14 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Let me pull out my crystal ball.


select empname,sum(empno) empno, sum(sal) sal from emp_table
group by empname
Re: simple query [message #278719 is a reply to message #278716] Mon, 05 November 2007 08:28 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
That would give 23 as sum(empno) and 35 as sum(sal)
The original poster probably has his data wrong; my bet is he simplified his acutal problem, but forgot to calculate the correct outcome
Re: simple query [message #278720 is a reply to message #278716] Mon, 05 November 2007 08:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Marc your crystal ball is broken.
You didn't select only "certain rows".

Regards
Michel
Re: simple query [message #278722 is a reply to message #278659] Mon, 05 November 2007 08:31 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Laughing
Re: simple query [message #278724 is a reply to message #278722] Mon, 05 November 2007 08:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Out of curiosity, why would you sum the Empno column?
Re: simple query [message #278725 is a reply to message #278724] Mon, 05 November 2007 08:37 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Why, to find out how many emps there are of course Very Happy
Re: simple query [message #278726 is a reply to message #278725] Mon, 05 November 2007 08:38 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Oh, but then you have to divide them by count(name), you silly.
Re: simple query [message #278735 is a reply to message #278659] Mon, 05 November 2007 09:07 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
thanks for the responses.
Quote:

The original poster probably has his data wrong

No, the data is not wrong.
Let me pull out my crystal ball.



select empname,sum(empno) empno, sum(sal) sal from emp_table
group by empname


This would exclude b.This is the problem.I have to sum up the no. of employees and there salaries .To be more accurate i have a table that has district and cities that come under district.
When queried district wise, i should get the sum of all the employees and there salaries.ie if a is a district and b is a city under it, then i should get the sum of cities + district.
Like i have mentioned in my OP.
Re: simple query [message #278737 is a reply to message #278726] Mon, 05 November 2007 09:14 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Laughing
Re: simple query [message #278738 is a reply to message #278735] Mon, 05 November 2007 09:15 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
varu123 wrote on Mon, 05 November 2007 16:07

ie if a is a district and b is a city under it, then i should get the sum of cities + district.
Like i have mentioned in my OP.


Don't you mean "Like I completely and utterly forgot to mention in my OP? WHERE in your OP do you mention city and district?

And WHY is the salary of employee B summed and displayed with the salary of employee A?

Seriously, so far the things you asked don't make any sense.

Re: simple query [message #278742 is a reply to message #278659] Mon, 05 November 2007 09:25 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
So you want a COUNT of employees then? Not the SUM of Emp NO ??

Please clarify this more, I can't spend any more time on this until you can tell us what you are looking for.
Re: simple query [message #278743 is a reply to message #278659] Mon, 05 November 2007 09:26 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
thats because i have to make a report that would display the total number of employees with the total amount of salaries drawn in all the cities(b)under there respective districts(a).
In the OP i meant the example i showed in the first post.
Empno is not employee number but the number of employees.
a is a district
b is a city under it.

[Updated on: Mon, 05 November 2007 09:28]

Report message to a moderator

Re: simple query [message #278744 is a reply to message #278659] Mon, 05 November 2007 09:28 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Please post the create table scripts for the relevant tables.

Post Inserts with representative data.

Post CLEAR examples of your expected results.

Put some effort in, you are the one asking for assistance.
Re: simple query [message #278748 is a reply to message #278659] Mon, 05 November 2007 09:33 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Arright,it seems its quite difficult to get the result with the table structure i have.
I will break the table into two tables one for district and other for the city.Then if problem persist..i will revert back.

thanks
Re: simple query [message #278750 is a reply to message #278659] Mon, 05 November 2007 09:35 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You should learn what Third Normal Form is when it come to table design.
Re: simple query [message #278752 is a reply to message #278659] Mon, 05 November 2007 09:41 Go to previous message
varu123
Messages: 754
Registered: October 2007
Senior Member
yes thats right but i havent created the tables and insert data.
Previous Topic: Standization in Oracle
Next Topic: COUNT of distinct values in 2 columns
Goto Forum:
  


Current Time: Sat Dec 10 18:29:03 CST 2016

Total time taken to generate the page: 0.15418 seconds