1

I tried the following query but it doesn't give the answer that I need

SELECT fname, SUM(salary) FROM employee

I need to have the all the fname records and the Sum value as the next column.

What I get from this query is only the first fname Record and the total summation of the salaries.(only the 1st fname value)

JibW
  • 4,538
  • 17
  • 66
  • 101

1 Answers1

4

You need to add GROUP BY

SELECT fname, SUM(salary) 
FROM employee
GROUP BY fname

Most RDBMSs would reject your original query as invalid.

Or in response to the comment to get the SUM from the whole table as an additional column if the OVER clause is supported you can use

SELECT fname, SUM(salary) OVER ()
FROM employee

And if it isn't you can use a non correlated sub query.

SELECT fname, (SELECT SUM(salary) FROM employee)
FROM employee
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • when I did that it gives all the records, but SUM_salary column it gives normal salaries corresponds to each employee. same result of this "SELECT fname, salary FROM employee" – JibW Oct 25 '11 at 11:45
  • @JibW - Ah, OK I think I understand what you need now. – Martin Smith Oct 25 '11 at 11:48
  • Hi Martin, Thank you very much. OVER() function did't work in mysql. Is it come under MS SQL??? The Other way gives the answer. Thanks again. But isn't there any otherway rather than wring a sub query??? – JibW Oct 25 '11 at 12:03
  • @JibW - Yes MySQL doesn't support `OVER` (Most other RDBMSs do now). To get it as an additional **column** you would need to evaluate it first. You can also use `SELECT fname, SUM(salary) FROM employee GROUP BY fname WITH ROLLUP` to get it as an additional **row** on the end which is probably more efficient. – Martin Smith Oct 25 '11 at 12:05