How Do I add A Column To Count SSN

[rderkis]

I have a data set, That was set up using a vary complicated querry. I had quite a bit of help by a gentleman who was vary vary good as sql.

Now I want to add a column that counts the number of identical [ssn] in each [ssn] group. In a sql it would look kind of like this "Select ...,...,..., Count[ssn] as SSNcount from....

How can I add the column without messing with the original querry?
Here is the original working query

SELECT
DISTINCT SSN,
Job_Dispatch_Date,
First_Name,
Last_Name,
CID_Phone,
CID_Name,
Call_Start_Time,
Password_Used,
Call_Type
FROM LoggedCalls
WHERE InStr(Call_Type,'Call') > 0
AND Job_Dispatch_Date IN (
SELECT Job_Dispatch_Date
FROM LoggedCalls AS Tmp
GROUP BY Job_Dispatch_Date, SSN
HAVING Count(*)>1 AND SSN = LoggedCalls.SSN
)
AND (
SELECT COUNT(*)
FROM LoggedCalls AS LoggedCalls1
WHERE LoggedCalls.SSN = LoggedCalls1.SSN
AND LoggedCalls.Job_Dispatch_Date=LoggedCalls1.Job_Dispatch_Date
AND LoggedCalls1.Password_Used IS NOT NULL
) >= 1
;

 

[LKBrwnDBA]

Remove DISTINCT and use GROUP BY function:

SELECT 
  SSN, 
  Job_Dispatch_Date, 
  First_Name, 
  Last_Name, 
  CID_Phone, 
  CID_Name, 
  Call_Start_Time, 
  Password_Used, 
  Call_Type,
  Count(*) As SSNCount
FROM LoggedCalls
WHERE ...
GROUP BY
  SSN, 
  Job_Dispatch_Date, 
  First_Name, 
  Last_Name, 
  CID_Phone, 
  CID_Name, 
  Call_Start_Time, 
  Password_Used, 
  Call_Type

PS: Unfortunately this will produce the count at the detail level stated in the GROUP BY function.

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[rderkis]

I messed the above sql up when I was pasting it this is the one I was working with.

SELECT DISTINCT SSN,
Job_Dispatch_Date,
First_Name,
Last_Name,
CID_Phone,
CID_Name,
Call_Start_Time,
Password_Used,
Call_Type
FROM(LoggedCalls)
WHERE instr( Call_Type,'Call') > 0 and
((( Job_Dispatch_Date) In (SELECT [Job_Dispatch_Date]
FROM [LoggedCalls] As Tmp
GROUP BY [Job_Dispatch_Date],
[SSN] HAVING Count(*)>1 And
[SSN] = [LoggedCalls].[SSN])) AND
((1)<=(SELECT Count(*)
FROM [LoggedCalls] As LoggedCalls1
WHERE SSN = LoggedCalls1.ssn and Job_Dispatch_Date=LoggedCalls1.Job_Dispatch_Date and LoggedCalls1.Password_Used is not null )))
ORDER BY SSN ,
Job_Dispatch_Date,
Call_Start_Time;"

 

[rderkis]

LKBrwnDBA I tried doing as you said but I get the error
dataAdapter.Fill(_dataSet, "LoggedCalls") Run-time exception thrown : System.Data.OleDb.OleDbException - You tried to execute a query that does not include the specified expression 'SSN' as part of an aggregate function.

How do I get rid of this error? My mofifyed query looks like this.

sqlString = "SELECT SSN, Job_Dispatch_Date, First_Name, Last_Name, CID_Phone, CID_Name, Call_Start_Time, Password_Used,Call_Type, Count(*) As SSNCount FROM(LoggedCalls)WHERE instr( Call_Type,'Call') > 0 and ((( Job_Dispatch_Date) In (SELECT [Job_Dispatch_Date] FROM [LoggedCalls] As Tmp GROUP BY [Job_Dispatch_Date],[SSN] HAVING Count(*)>1 And [SSN] = [LoggedCalls].[SSN])) AND ((1)<=(SELECT Count(*) from [LoggedCalls] As LoggedCalls1 WHERE ssn = LoggedCalls1.ssn and Job_Dispatch_Date=LoggedCalls1.Job_Dispatch_Date and LoggedCalls1.Password_Used is not null ))) ORDER BY SSN , Job_Dispatch_Date, Call_Start_Time;"

I know this looks raged pasted this way but everytime I try to format it, so it is more readable I lose somthing Please forgive me because I do care.

 

[Zathras]

What you are asking for seems illogical since you have multiple lines for each SSN. Perhaps if you gave us some test data and a picture of the desired results someone could come up with something.

What is the desired result set for this simulated input data:
[tt]
SSN Date Name CID_Name Time Password Type
--- ---- ---- -------- ---- -------- ----
123 12/1 Joe Sue 4:00 GREEN A
123 12/2 Joe Sue 5:00 BLUE B
124 12/1 Sam Jill 6:00 HORSEY A
124 12/2 Sam Jill 6:30 DONKEY A
124 12/2 Sam Jill 7:00 HORSEY B
[/tt]
Is this what you would want to see?
[tt]
SSN Date Name CID_Name Time Password Type Count
--- ---- ---- -------- ---- -------- ---- -----
123 12/1 Joe Sue 4:00 GREEN A 2
123 12/2 Joe Sue 5:00 BLUE B 2
124 12/1 Sam Jill 6:00 HORSEY A 3
124 12/2 Sam Jill 6:30 DONKEY A 3
124 12/2 Sam Jill 7:00 HORSEY B 3
[/tt]
If so, then I think the only way is to develop a secondary data set that you can join with the first. Various techniques would work, create a temporary table, create a view, or make the original query much more complex.

 

[LKBrwnDBA]

You forgot the GROUP BY expression in your SQL statement.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[rderkis]

Well as it worked out I did not need any query more complicated than

select * from loggedcalls where suspious = "Yes"

I did this by evaluating each call as it came in and checking whether it meet the criteria as a suspious call. I was looking for a work around for the complicated sql I was not good enough to write. And as it works out this is much much better

I do appreate the time and effort that you all donated to me. Thank you!