Help with a select statement - 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 with a select statement

Author

Topic

Carl_Hardy
Starting Member

5 Posts

Posted - 2010-09-24 : 13:05:01

Hi, reasonably new to SQL programming and still getting tripped up by what some will see as basic SQL techniques.....here goes.

I have a table with the Columns: Job_Role, User_ID
There are many people with the same job and a user can have more than one job.

What I need is a summary of the Jobs by user, but here is the bit that confuses me. I also need to have a total of the number of people with the same job.

Job_Role User_ID Users_With_Job
Job1.......Carl......16
Job2.......Carl......83
Job12.....Carl......112
Job86.....Carl......2

I currently have two views set up to provide the Jobs by User and another to provide a total number of users with the Job.
I then have a third viewed on the other two to combine the figures.

Although it works, it seems a bit clunky. Any suggestions how to do this with one View (or sp)

Carl

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-24 : 13:09:26

Well it's confusing because ou are combing 2 different things in 1 result set

Thing 1 Job's a User Has
Thing 2 # of Users with a Job

The commonailty is the Job

So do use a favor and write 2 separate sql's...1 for thing, the other for thing 2...

And post sample data for us in the form of

INSERT INTO myTable(Job_Role, User_ID)
SELECT 'Job data', 'User Id' UNION ALL
SELECT 'Job data', 'User Id' UNION ALL
ect

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

Carl_Hardy
Starting Member

5 Posts

Posted - 2010-09-24 : 16:47:29

No worries, I will write a sp using a temporary table. I thought someone might have an easier answer using subquery/nested select.

Topic can be closed.

Carl

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-25 : 00:05:06

if sql 2005 and above, you can do like this


CREATE VIEW YourView
AS
BEGIN
SELECT  Job_Role,User_ID,COUNT(Job_ID) OVER (PARTITION BY User_ID) AS JobsByUser,COUNT(User_ID) OVER (PARTITION BY Job_ID) AS UsersByJobs
FROM Table
END

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

Carl_Hardy
Starting Member

5 Posts

Posted - 2010-09-25 : 03:10:28

Thanks visakh16,
'OVER' and 'PARTITIION BY' are new ones to me, I will give it a go.

Carl

Carl_Hardy
Starting Member

5 Posts

Posted - 2010-09-25 : 10:16:01

Even though I am running SQL server 2005 I am getting the message that 'over' is not supported.

To ensure full understanding of what I am after:
I want to return a list of all the Jobs a specific user has and for each job in the list, show the number of other users with the same job.

Carl

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-25 : 12:05:52

quote:
Originally posted by Carl_Hardy

Even though I am running SQL server 2005 I am getting the message that 'over' is not supported.

To ensure full understanding of what I am after:
I want to return a list of all the Jobs a specific user has and for each job in the list, show the number of other users with the same job.

Carl

Then your compatibility level will be less than 90. check it first


EXEC sp_dbcmptlevel 'your db name'

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

Carl_Hardy
Starting Member

5 Posts

Posted - 2010-09-26 : 07:44:27

did as you suggested and the message said curretn compatibility is at 90. Very strange.
Learned something else new checking the compatibility.

Carl

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-26 : 07:52:32

which editor are you using?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources