?
Solved

SQL count statement

Posted on 2009-07-14
6
Medium Priority
?
251 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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 2000 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

752 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