Solved

SQL count statement

Posted on 2009-07-14
6
229 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 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

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…
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…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

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