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 2000 Forums
 Transact-SQL (2000)
 Generate new column showing count.

Author  Topic 

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2006-02-15 : 10:09:21
Can anyone please tell me if it is possible to amend my query so it does a count of all the AgentID's that belong to a specific SMID in the table below and display them on a 1 row basis. So, for example SMID 106426 would be displyed on just one row with a new column holding the value '4' as 4 is the no. of AgentID's that are related to this SMID. SMID 106432 would have the No. 12 as a value and so on.

The query I have used to get thus far is:
SELECT SMID, AgentID
FROM dbo.vw_TimesheetFact
where perctarget < 80 AND ([date] BETWEEN CONVERT(varchar, DATEADD(d, - 21, GETDATE()), 112) AND CONVERT(varchar, GETDATE(), 112))
AND perctarget > 0
order by SMID

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2006-02-15 : 10:16:47
Don't worry about the duplicate ID's in the AgentID column, I forgot to put the distinct in before I took the screenshot.
Go to Top of Page

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-02-15 : 10:17:03
[code]SELECT SMID, COUNT(DISTINCT AgentID)
FROM dbo.vw_TimesheetFact
where perctarget < 80 AND ([date] BETWEEN CONVERT(varchar, DATEADD(d, - 21, GETDATE()), 112) AND CONVERT(varchar, GETDATE(), 112))
AND perctarget > 0
GROUP BY SMID
order by SMID
[/code]

Mark
Go to Top of Page

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2006-02-15 : 10:31:43
Many thanks Mark, it works perfectly. I was nearly there but was putting my DISTINCT in the wrong place. These things are so obvious with the benefit of hindsight!!

Cheers, Pat.
Go to Top of Page

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-02-16 : 07:24:09
No problem Pat

Mark
Go to Top of Page
   

- Advertisement -