Count() issue

[mcgraw]

I have what should have been a relatively easy Count() query that I just can't get to return the correct information.

Here's what I have:

SELECT ci_lookup_status.CL_STATUS, Count(ci_lookup_status.CL_STATUS) AS CountOfCL_STATUS
FROM ci_issue_status INNER JOIN ci_lookup_status ON ci_issue_status.FK_CLK_STATUS = ci_lookup_status.PK_CL_ID
GROUP BY ci_lookup_status.CL_STATUS
HAVING (((ci_lookup_status.CL_STATUS)<>'Legacy Comments' And (ci_lookup_status.CL_STATUS)<>'Unfunded' And (ci_lookup_status.CL_STATUS)<>'Cancelled'));

The issue is, a field ci_issue_status.FK_CI_ISSUE_ID (the issue ID from another table) has multiple entries...and I need it to return only the current (LAST) entry for each ID.

If I add FK_CI_ISSUE_ID into the query, I get a count of 1 for each entry, so how do I get it to pull the last entry for each ID, and then count the number for each status?

Any help would be GREATLY appreciated!

 

[Galaxiom]

Use a subquery to return the record you require.

However do not be tempted to use the LAST aggregate function as it is somewhat unpredictable. Use a field such as Date/Time or some number with the MAX aggregate function.

I would also recommend normalising the CL_STATUS field rather than recording a string as the value.

 

[mcgraw]

Thanks. I'm trying to do the subquery using the MAX function, and i'm having no luck.

I have a simple query that is returning the MAX for the created date, but the second I try to put it into a subquery and add cl_status, it seems to remove the groupings.

My simple query is this

SELECT 
     ci_issue_status.FK_CI_ISSUE_ID, 
     Max(ci_issue_status.CS_CREATE_DTS) AS MaxOfCS_CREATE_DTS

FROM 
     ci_issue_status LEFT JOIN ci_lookup_status ON  ci_issue_status.FK_CLK_STATUS = ci_lookup_status.PK_CL_ID

GROUP BY 
ci_issue_status.FK_CI_ISSUE_ID;

So, how would I take this query and put it into a sub and pull the status, and a count of the status from the above query?

Kinda new to more advanced queries that don't require pulling very basic info...so I'm just getting myself confused. Appreciate the help!

 

[mcgraw]

oh, and as far as normalising the data, I have no control over the DB, or the tables...I'm just "allowed" to do datamining and pull reports.

 

[boblarson]

Personally, I get confused when trying to write it all into a single query using subqueries. I tend, in this case, to write a query that has the max date for the ID. Then I open the query designer and add that query along with the regular tables in and link both ID and DATE to the original table from the query I just created for the max date.