Solved

SQL count statement

Posted on 2009-07-14
6
221 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
  • 3
  • 3
6 Comments
 
LVL 142

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 142

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 142

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

809 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