RE: SQL... MAX AND COUNT?

From: Wilt, Charles

Well I think I answered it ;-)

But maybe not.  Then again, I'm not sure you answered it either Joe.

The OP said, "but I ONLY need those latest dates considered in the
count" so I think he's going to need MAX in there some where.  So I
think your second solution is out.  As to the first solution, even
though you've got MAX in there it's not being used to select the records
to count.

It depends on whether there can be one record per date, or multiple records
per date.  If there can only be one record per date then there can only be
one record with the latest date and DISTINCT will work.  If there can be
multiple records on a given date and you want a count of all records with
the latest date, then you're on more of the correct track.

So as always, it's an issue of really defining the question.

Unless I'm misunderstanding what the OP wants, I'm going to offer up my
solution again in case it was missed.

 SELECT COUNT(LDSUBC) INTO :WKCOUNT
 FROM BANKHEDR INNER JOIN LRDETAIL
         ON QHSTCD = LDSTCD AND QHAREA = LDAREA AND QHBANK =
         LDBANK AND LDDGRP = QHHGRP AND LDCATC = :WKCATCD
 WHERE QHHGRP = :WKGROUP AND QHRSTA = 'A' AND QHMAIN = :WKMAIN
   and LDQDAT = ( SELECT MAX(LDQDAT)
                  FROM LRDETAIL
                )

Side note to the OP:
Are you sure that COUNT(LDSUB) is what you want?  To get a count of the
number of rows, one would normally use just COUNT(*).  COUNT(LDSUBC)
gives you the count of the number of non-null values in LDSUBC.
COUNT(DISTINCT LDSUBC) gives you the count of distinct non-null values.

This is interesting, in that it includes only records which match the latest
date of ANY record in the file.  Thus, if there are 20 records and five of
them from one set of keys have today's date, and the other 15 come from 15
different unique keys but have yesterday's date, then the count is five.

Whereas in my scenario, I would get 16 (one entry from each unique key).

My problem with your approach, Charles, is that it uses the highest date in
the whole LRDETAIL file.  Even if Jay wanted a count of all the records on
the highest date, then there still the question: should you limit your
SUBSELECT to only the records that match the original selection criteria?
In your case, MAX(LDQDAT) could return a date from a record that isn't even
in the select criteria, so the count would be zero!

Ah, the joy of SQL.

Joe