Getting a COUNT(*) from query using HAVING?

[cmayo]

Hi all,

I have a situation where I need to get the count of reords in one table which have no corresponding records in another table. The only way I've made it work so far is by using a HAVING clause, which seems to preclude the possibility of using COUNT(*)... or at least when I try to COUNT this query, I get a zero count.

SELECT 
	count(*),
	count(service_timesheet.start_time) as checkin

FROM 
	(services serv, 
	customers cust, 
	job_sites work)

LEFT JOIN
	service_timesheet on (serv.service_id = service_timesheet.service_id )

WHERE 
	serv.customer_id = cust.customer_id 
	and 
	serv.job_site_id = work.job_site_id


	and
	serv.disp_date >= '2008-08-01'
	and
	serv.order_status >= 'Open'
	and
	NOW() > concat(disp_date, ' 23:39:00')
	

GROUP BY 
	serv.service_id

HAVING
    checkin = 0

The query above returns two records (below), but I need a query which will return a COUNT(*) type result without having to return the entire record set.

+----------+---------+
| count(*) | checkin |
+----------+---------+
|        1 |       0 | 
|        1 |       0 | 
+----------+---------+
2 rows in set (0.01 sec)

Any ideas?

Thanks in advance,
Chuck

 

[IanWaterman]

TRy
SELECT
count(*), serv.service_id

FROM
(services serv,
customers cust,
job_sites work)

LEFT JOIN
service_timesheet on (serv.service_id = service_timesheet.service_id )

WHERE
serv.customer_id = cust.customer_id
and
serv.job_site_id = work.job_site_id


and
serv.disp_date >= '2008-08-01'
and
serv.order_status >= 'Open'
and
NOW() > concat(disp_date, ' 23:39:00')


GROUP BY
serv.service_id

HAVING
count(service_timesheet.start_time) = 0

 

[cmayo]

Ian,

Good thought, but still doesn't return a single count value:

+----------+------------+
| count(*) | service_id |
+----------+------------+
|        1 |        956 | 
|        1 |        972 | 
|        1 |        973 | 
|        1 |        974 | 
+----------+------------+
4 rows in set (0.02 sec)

My original query returns the following today:

+----------+---------+
| count(*) | checkin |
+----------+---------+
|        1 |       0 | 
|        1 |       0 | 
|        1 |       0 | 
|        1 |       0 | 
+----------+---------+
4 rows in set (0.02 sec)

 

[IanWaterman]

A quick and easy cheat is
select Count(*) from
(SELECT
count(*), serv.service_id

FROM
(services serv,
customers cust,
job_sites work)

LEFT JOIN
service_timesheet on (serv.service_id = service_timesheet.service_id )

WHERE
serv.customer_id = cust.customer_id
and
serv.job_site_id = work.job_site_id


and
serv.disp_date >= '2008-08-01'
and
serv.order_status >= 'Open'
and
NOW() > concat(disp_date, ' 23:39:00')


GROUP BY
serv.service_id

HAVING
count(service_timesheet.start_time) = 0
)

Ian

 

[AdaHacker]

You want a count of the rows without a service_timesheet record, right? So just exploit the fact that you're doing a left outer join on that table and count the rows with null service_timesheet.service_id. For instance:

SELECT 
    count(*)

FROM 
    (services serv, 
    customers cust, 
    job_sites work)

LEFT JOIN
    service_timesheet on (serv.service_id = service_timesheet.service_id )

WHERE 
    serv.customer_id = cust.customer_id 
    and 
    serv.job_site_id = work.job_site_id
    and
    serv.disp_date >= '2008-08-01'
    and
    serv.order_status >= 'Open'
    and
    NOW() > concat(disp_date, ' 23:39:00')
    AND 
    service_timestamp.service_id IS NULL

 

[cmayo]

Thanks again Ian. The query you suggested returns a message about all derived tables needing an alias, and I haven't had time to research that error and try to clear the error.

AdaHacker, thanks for your response. Your query returns what I'm looking for except that instead of counting rows without a service_timesheet, I need to return rows without a service_timesheet.start_time. Again, I haven't had a chance to dig into the query and try to amend it... can you see a way to get a count of those records without a service_timesheet.start_time?

 

[AdaHacker]

can you see a way to get a count of those records without a service_timesheet.start_time?

Um, yeah...change the last condition in the WHERE clause to check for start_time instead of service_id.

You could also drop that last condition and switch the COUNT(*) to a COUNT(service_timesheet.start_time), like you had before. Aggregate functions drop NULLs, so you should get the same result. That's assuming, of course, that your "empty" start_time is actually NULL in the database.