Help - new member - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help - new member

Author

Topic

Orange_SQL
Starting Member

8 Posts

Posted - 2011-03-28 : 15:21:56

Hello all,

I have quite a few questions but wanted to start with the following;

I have written a query which works but I need the result to be shown to zero decimal places:

select(select SUM(Annual_Salary) from Employees where Employees.Department_No in ('80','60'))
/12 as Total_Monthly_Salaries

Any help would be much appreciated!

Thanks

Orange

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-28 : 15:38:19

select CAST(SUM(Annual_Salary)/12 AS int) as Total_Monthly_Salaries
from Employees where Employees.Department_No in ('80','60')

Orange_SQL
Starting Member

8 Posts

Posted - 2011-03-28 : 15:50:43

Thanks a million - works a treat!

I'm just working through some older questions which I can't remember how to solve so any help is great!!!

Orange_SQL
Starting Member

8 Posts

Posted - 2011-03-28 : 15:54:52

Another one I can't get to work:

select round(AVG(annual_salary),(2)) as AVG_Annual_Salary_for_1998 from Employees where
hire_date between '1998-01-01' and '1998-12-31'

This query will not show the result to 2 decimal places, instead it shows: 14290.910000. Can it be done to show the result without showing the "0000" at the end???

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-28 : 16:01:26

SELECT CAST(AVG(annual_salary) AS DECIMAL(18,2)) AS AVG_Annual_Salary_for_1998
FROM Employees
WHERE hire_date BETWEEN '1998-01-01' AND '1998-12-31'

If these queries are for reports, it's better to do the number formatting in the report layer, not the query.

Orange_SQL
Starting Member

8 Posts

Posted - 2011-03-28 : 16:07:47

Thank you very much but I can't even remember how to do the formatting in the report layer...

Orange_SQL
Starting Member

8 Posts

Posted - 2011-03-28 : 17:39:22

Another one if anyone is able to help...

I wish to update all entries in a column named "min_salary" by 500 from dbo.jobs

Any clues???

Thank you in advance and many thanks again for the help so far robvolk

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-28 : 17:41:38

UPDATE dbo.Jobs SET min_salary=min_salary + 500

Orange_SQL
Starting Member

8 Posts

Posted - 2011-03-28 : 17:54:04

Thanks Rob,

is there any way of checking that the update task has been executed correctly or would I have to check the old records?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-28 : 17:56:18

SELECT MIN(min_salary) min_before, MAX(min_salary) max_before FROM myTable
UPDATE dbo.Jobs SET min_salary=min_salary + 500
SELECT MIN(min_salary) min_after, MAX(min_salary) max_after FROM myTable -- should be $500 more for each

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-03-28 : 18:04:18

What reporting tool are you using? If SSRS - then you format the text box as a number and define the number of decimal places you want.

Orange_SQL
Starting Member

8 Posts

Posted - 2011-03-28 : 18:38:53

Slowly getting the hang of this again but can't get yet another to work:

select Employees.Department_No, Departments.Department_Name
from Employees right join Departments on
Employees.Department_No = Employees.Department_No
where COUNT(employees.Department_No) <4
group by Employees.Department_No, Departments.Department_Name

I wish to only list departments with less than 4 employees using employees.department_no

I need to show only employees for each department with less than 4 employees...

Can't thank you enough Rob, will have to start adding to your salary soon :-) lol

Orange_SQL
Starting Member

8 Posts

Posted - 2011-03-28 : 19:04:26

Think I nearly have it but result shows with no column name???

select count(Employees.Department_No), Employees.Department_No, Departments.Department_Name
from Employees join Departments
on Employees.Department_No=Departments.Department_No
group by Employees.Department_No, Departments.Department_Name
having COUNT(Employees.Department_No)<4

matty
Posting Yak Master

161 Posts

Posted - 2011-03-29 : 02:39:16

quote:
Originally posted by Orange_SQL

Think I nearly have it but result shows with no column name???

select count(Employees.Department_No) AS EmpCount, Employees.Department_No, Departments.Department_Name
from Employees join Departments
on Employees.Department_No=Departments.Department_No
group by Employees.Department_No, Departments.Department_Name
having COUNT(Employees.Department_No)<4

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources