asked on

SQL count statement

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.

Guy Hengel [angelIII / a3]

you mean:

SELECT Agent_ID  , COUNT(*) 
FROM DIPApplication
WHERE DIPProcessed=1
group by Agent_ID

Open in new window

amillyard

ASKER

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]

amillyard

ASKER

:: only thing missing from query was filtering from 1st Sep 2008

Guy Hengel [angelIII / a3]

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

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

amillyard

ASKER

A+++ response (timing, clarity of scripting/knowledge share) -- apprieated :-)