where query, return results - 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

where query, return results

Author

Topic

pavlos
Yak Posting Veteran

59 Posts

Posted - 2010-05-09 : 03:20:16

Hey guys,
I have been asked to help my uncle with his company and the use sql server manager. I can do basic sql queries and counts by groups etc. But that is all.
Anyways to my problem

He wants to know how many different employees have had more than 3 roles in the same state. If that makes sense.

So for example:
One employee may have been an accountant and given a role number and then a area manager and given a new role number. He wants to findout how many people have had 3 or more jobs in a particular state.

He wants me to be able to return the employees name, amount of jobs and the state.

The tables I am looking at are the 'employee' table with a list of employee details with employee id, a 'role' table that has all the role numbers of a particular job and what state they where in.

I thought the best way of doing it would be to
count all the roles of a person in a particular state

English is my second language and I am not great with sql.

I know how to count the all roles of an employee but then not be able to tie it to a particualr state.

pavlos
Yak Posting Veteran

59 Posts

Posted - 2010-05-09 : 03:57:55

sorry guys, i read over this and i want to make it more clear.
i can work out all the results from one table

--code
select role, id, state from employee

this returns
role number id state
1 2 kozani
2 3 kastoria
3 2 kozani

so
i want to work out a count of role numbers to id in the same state

so i want to return the following

total roles id number state
6 2 kozani

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-09 : 04:00:32

Try group by on ID and State and take count(role)

pavlos
Yak Posting Veteran

59 Posts

Posted - 2010-05-09 : 04:08:21

you are a guru!
thanks friend.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-09 : 04:24:12

I m not guru... Just a beginner...
The Gurus are Tara,visakh,Peso,Madhi,Kristen, khtan.

You are welcome

pavlos
Yak Posting Veteran

59 Posts

Posted - 2010-05-09 : 04:39:25

ok one last question:D
when i am putting in the where clause
it counts all the integers and converts them to varchar value

This is the error message i recieve
Conversion failed when converting the varchar value 'Position Count' to data type int

how do i get around this?
cheers

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-09 : 04:52:28

Can you post the query you are trying ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-09 : 05:05:36

quote:
Originally posted by pavlos

ok one last question:D
when i am putting in the where clause
it counts all the integers and converts them to varchar value

This is the error message i recieve
Conversion failed when converting the varchar value 'Position Count' to data type int

how do i get around this?
cheers

are you comparing varchar column to numeric data in where condition?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pavlos
Yak Posting Veteran

59 Posts

Posted - 2010-05-09 : 05:12:39

Select E.Name , S.State, count(RoleId) as 'position'
FROM employee E

inner join State as S
on E.EmployeeID = S.EmployeeID

WHERE 'position' > 2

group by e.name, s.state

pavlos
Yak Posting Veteran

59 Posts

Posted - 2010-05-09 : 05:14:38

i have moved the where clause but doesn't make a difference
i also tried doing where 'position' = '2'
to get a result and it executes and returns nothing.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-09 : 05:23:09

quote:
Originally posted by pavlos

i have moved the where clause but doesn't make a difference
i also tried doing where 'position' = '2'
to get a result and it executes and returns nothing.

you dont need ' around position if its a column in a table

where position > 2
is enough

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pavlos
Yak Posting Veteran

59 Posts

Posted - 2010-05-09 : 09:00:11

hey friends,
i use the having clause.
for the count
and it works!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-10 : 13:24:23

so you were using aggregation. you can use aggregates only in HAVING

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources