SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Generate new column showing count.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

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

United Kingdom
103 Posts

Posted - 02/15/2006 :  10:09:21  Show Profile  Reply with Quote
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

United Kingdom
103 Posts

Posted - 02/15/2006 :  10:16:47  Show Profile  Reply with Quote
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

United Kingdom
735 Posts

Posted - 02/15/2006 :  10:17:03  Show Profile  Reply with Quote
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


Mark
Go to Top of Page

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

United Kingdom
103 Posts

Posted - 02/15/2006 :  10:31:43  Show Profile  Reply with Quote
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

United Kingdom
735 Posts

Posted - 02/16/2006 :  07:24:09  Show Profile  Reply with Quote
No problem Pat

Mark
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000