Two counts

[betheball]

I think it likely this can't be done, but thought I would ask. I need to try and get two counts with one SQL. I want a total record count grouped by month and then a count of only those records where a certain field value is "Yes" also grouped by month. As I write this it is seeming less and less possible. Is it?

 

[IanH]

It's actually quite simple. If you have a table of data (called DataTable in the SQL code below) with a date field (called 'Date'), and a Yes/No flag field (called 'Flag'), then as you say you need to group on the month, count how many records for each month, and then count how many records where the flag is 'Yes'.

The SQL code would look like this ...

SELECT Month([Date]) AS [Month], Count(*) AS RecordCount, Sum(IIf([Flag]=Yes,1,0)) AS FlaggedRecords
FROM DataTable
GROUP BY Month([Date]);

Effectively what you ask for when you use the COUNT(*) function, is a count of every record. It is equivalent to SUM(1), which is like a running sum, adding 1 for every record it counts. What this syntax does is SUM(1), but only for a selected set of records (the IIf function). If the field value doesn't match your criteria, it adds 0 to your running sum, instead of 1.

The syntax is similar for any value you want to evaluate against (=, <>, <, >). For example if you have 'Y' and 'N' in a text field instead of a Yes/No field, use:

Sum(IIf([Flag]='Y',1,0)) AS FlaggedRecords

If you have a currency field and wanted to only choose records with over £1000 in it use:

Sum(IIf([MoneyField]>1000,1,0)) AS FlaggedRecords

Best regards

Ian

 

[betheball]

Outstanding! Kind of like a little WHERE clause. I probably should have know that. Very much appreciated. That will save me having to spend hours learning arrays for an ASP page I am creating.

 

[Kodo]

Outstanding! Kind of like a little WHERE clause. I probably should have know that. Very much appreciated. That will save me having to spend hours learning arrays for an ASP page I am creating.

Don't NOT learn arrays.. in ASP, they will have a dramatic impact on the speed of your apps. Slapping records into getrows or getstring and immediately releasing the resources is a good thing!!s

aside from that..is this an access db or sql server db that you're using?

 

[betheball]

Access.

I am sure I will learn arrays. However, I have been on what I consider a pretty steep learning curve and feel my hard drive is about maxed out.