syntax question

[mjd3000]

I need to do a record count based upon i) fields being equal to a given value, or ii) the count of another query being > 0.

Here is my SQL at the moment :

This is the bit where I am checking for a valid a.activitytypecode and a valid l.createdonutc (date) :

(select count(*)
from filteredlead l
inner join filteredactivitypointer a
on l.leadid = a.regardingobjectid
where a.activitytypecode in (4201,4401)
and convert(char(10),l.createdonutc,103) = convert(char(10),getdate(),103)

or

(select count(a.activityid)
from filteredlead l
inner join filteredactivitypointer a
on l.leadid = a.regardingobjectid
inner join filteredphonecall p
on a.activityid = p.activityid
where a.activitytypecode = 4210
and p.new_calloutcome in (1,3,4,5,6,7)
and convert(char(10),l.createdonutc,103) = convert(char(10),getdate(),103)) < 0

And this the part where I am checking that another count < 0. Can anybody tell me what I am doing wrong here, I know my syntax isn't quite right but I'm not sure how to correct it.

 

[dodge20]

What field are you counting? Do you want the count > 0, count less than 0 doesn't make sense.

If you want a condition using count you need to use a group by with a having clause.

select field1, count(field2)
from table
group by field1
having count(field2) > 0.


Dodge20

 

[bborissov]

COUNT(*) counts ALL records that match WHERE clause no matter of fields value.

COUNT(Field) counts ALL records that match WHERE clause AND where the field you count is NOT null.


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[PGO01]

Not 100% sure what you're trying to do exactly but I think you want to combine both queries into 1 something like this:

SELECT COUNT(*)
FROM filteredlead l
		INNER JOIN filteredactivitypointer a ON l.leadid = a.regardingobjectid
		INNER JOIN filteredphonecall p ON a.activityid = p.activityid
WHERE 
	(a.activitytypecode IN (4201, 4401) 
		AND CONVERT(CHAR(10), l.createdonutc, 103) = CONVERT(CHAR(10), GETDATE(), 103))
	OR
	(a.activitytypecode = 4210 
		AND p.new_calloutcome IN (1, 3, 4, 5, 6, 7) 
		AND CONVERT(CHAR(10), l.createdonutc, 103) = CONVERT(CHAR(10), GETDATE(), 103))