When I first posted this, the sql query seemed to work fine, but after a bit of testing I noticed that node revisions will muck it. The query needed to get more complicated to look for the max vid instead of just relying on the nid. The symptom was that I would delete a nodereference, but it would still be counted (it would still count the previous revision's value). The revised computed field value is below.
Computed Field module asks us to add code snippets to the handbook pages, but they're nowhere to be found after the D6 upgrade. http://drupal.org/node/126522
Here's one that I just created to calculate the number of nodereferences for a cck type.
Event (includes datestamp field and is node referenced to Venue)
We wanted to create a view of Venues with upcoming events, including the number of events in parentheses. LIke so:
Venue X (2)
Venue Y (1)
Created a computed field for Venue type, stored in db as int(10), and added the following code:
THIS IS NO GOOD WHEN USING NODE REVISIONS:
$node_field['value'] = db_result(db_query("SELECT COUNT(DISTINCT cte.nid) FROM content_type_event cte INNER JOIN node n ON cte.nid = n.nid where cte.field_venue_nid = $node->nid AND n.status = 1 AND cte.field_datestamp_value >= UNIX_TIMESTAMP()"));
WHEN USING NODE REVISIONS, IT SHOULD LOOK LIKE THIS (it's a self join on the content type table to look at the latest revision):
$node_field['value'] = db_result(db_query("select count(distinct cte.nid) from (select nid, max(vid) as maxvid from content_type_event group by nid) as x inner join (content_type_event cte inner join node n on cte.nid= n.nid) on cte.nid = x.nid and cte.vid = x.maxvid where cte.field_venue_nid = $node->nid and n.status = 1 and cte.field_datestamp_value >= UNIX_TIMESTAMP()"));
Created a view with filter computed field >= 0
Hope this helps someone.