Solved

SQL count statement

Posted on 2009-07-14
6
238 Views
Last Modified: 2012-05-07
Table DIPApplication has 'x' records -- and
Agent_ID can be represented in many of these records, i.e. Data Entry ser stamp in record.
want to be able to produce a sql result where I can return:

[Agent_ID], [how many times DIPProcessed] from Sept 1st 2009

e.g. how many records that have DIPProcessed=1 associated with Agent_ID from Sept 1st 2009

list all unique Agent_ID's in table, along with the sum of their respective DIPProcessed

how do I write this type of query please?

Table:  DIPApplication  
Agent Ref.:  Agent_ID  (various int id's)
DIP Processed:   DIPProcessed = 1 (means processed)
DateTime:  DateTimeStamp  (DateTime)

(using SQL Server Management Studio interface)

I can get the entire number of DIP's Processed as follows:

SELECT COUNT(*)
FROM DIPApplication
WHERE DIPProcessed=1

Just need to expand this query as described above.
0
Comment
Question by:amillyard
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24848912
you mean:
SELECT Agent_ID  , COUNT(*) 
FROM DIPApplication
WHERE DIPProcessed=1
group by Agent_ID  

Open in new window

0
 

Author Comment

by:amillyard
ID: 24848974
angelIII: yes, excellent 100% correct results.  thank you.

could I ask please 1 more thing related to above -- if I want to cross-reference that with another table.  i.e. to retrieve the Agent's name instead of just Agent_ID number value (more meaninful when reading)


Table: StaffMember
Agent_ID [int]
Agent Name: DisplayName  [nvarchar]
0
 

Author Comment

by:amillyard
ID: 24849003
:: only thing missing from query was filtering from 1st Sep 2008
0
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24849014
null problemo:
SELECT app.Agent_ID , a.displayname , COUNT(*) 
FROM DIPApplication app
JOIN agent a
  on a.agent_id = app.agent_id
WHERE app.DIPProcessed=1
group by app.Agent_ID  , a.displayname

Open in new window

0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24849026
and the time condition
SELECT app.Agent_ID , a.displayname , COUNT(*) 
FROM DIPApplication app
JOIN agent a
  on a.agent_id = app.agent_id
WHERE app.DIPProcessed=1
  and app.DateTimeStamp >= convert(datetime, '2008-09-01', 120)
group by app.Agent_ID  , a.displayname

Open in new window

0
 

Author Closing Comment

by:amillyard
ID: 31603226
A+++ response (timing, clarity of scripting/knowledge share) -- apprieated :-)
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question