SQL code needed for Query

Question

Hi all,

I need SQL code for a query I need to do in Access. I have a table with multiple fields but I only need 2 fields for the query. 1 is a date field. let's call that one DateField. the other is a field that contains text, let's call lit TextField. I need a query that returns the following:

1. The COUNT of DateField (i.e. how many there are regardless of what the value is)
2. The COUNT of TextField WHERE its value = "ThisText"
3. The COUNT of TextField WHERE its value = "ThatText"
4. Results GROUP BY Year
5. the same query again (will be a separate Q) but with results GROUP BY Month

Many thanks in advance for all your wonderful help.

Answer 1

I believe you can only SELECT a given aggregate function once per per query. That is to say you cannot request the COUNT two different fields in a single query. Here's the reference for the count function in JET SQL. At best you can count the number of non-NULL values of a certain field in a grouped result set under some WHERE clause.

Answer 2

How about:

SELECT 
   Year
   ,COUNT(DISTINCT DateField) AS NumDateFields
   ,COUNT(CASE WHEN TextField = 'ThisText' THEN 1 END) AS ThisTextCount
   ,COUNT(CASE WHEN TextField = 'ThatText' THEN 1 END) AS ThisTextCount
GROUP BY Year;

Or... GROUP BY Month

Answer 3

SELECT YEAR/MONTH(DateField), COUNT(DateField), 
       SUM(IIF(TextField='ThisText', 1, 0)),
       SUM(IIF(TextField='ThatText', 1, 0))
FROM MyTable
GROUP BY YEAR/MONTH(DateField)