get count - 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

SQL Server 2008 Forums

Transact-SQL (2008)

get count

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2014-11-06 : 12:17:06

I have 2 tables

Employee and Positions

Employee
id
empid
Name

Position
empid
posName

I want to do a select on employee and bring back one employee record and add a count of how many positions he has

Dave
Helixpoint Web Development
http://www.helixpoint.com

AASC
Starting Member

24 Posts

Posted - 2014-11-06 : 13:19:22

--Temporary table creation
CREATE TABLE #employee
( id INT,
empid INT,
name VARCHAR(100)
)

CREATE TABLE #positions
( empid INT,
Posname VARCHAR(200)
)

--- dumpy data insert
INSERT INTO #employee
SELECT 1, 1, 'Rocky'
UNION ALL
SELECT 2,200,'Sam'

INSERT INTO #positions
SELECT 1,'PM'
UNION ALL
SELECT 1,'BOP'
UNION
SELECT 2, 'L'
UNION SELECT 2, 'M'

--- Query to select employee with count of positions
SELECT emp.id,emp.empid,emp.name ,COUNT(pos.Posname)
FROM #employee emp
INNER JOIN #positions pos ON emp.empid=pos.empid
WHERE emp.name='rocky'
GROUP BY emp.id,emp.empid,emp.name

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources