Counting Multiple Columns that meet a specified condition

[masb13]

I have a table with a large number of columns. Among the columns are four columns that hold the expiry dates for different types of insurance. I need to write a query that counts the number of records, where the date difference on any of the four columns, between the date now and the expiry date is within the next 30 days. The data will eventually be used in a dashboard to flag the number of insurance policies expiring in the next 30 days, so the results will need to be grouped by company.

The following select statement will return all records where the expiry date is within the 30 days, but I am unable to turn this statement into a SELECT COUNT query without getting an error "You tried to execute a query that does not include the specified expression Expr2 as part of an aggregate function". Can anyone tell me what is wrong, and perhaps show me how to get this working.

SELECT (PI_EXPIRY_DATE) AS Expr1, (WC_expiry_date) AS Expr2, (EL_expiry_date) AS Expr3, (CAR_expiry_date) AS Expr4
FROM Company
WHERE (((Company.PI_EXPIRY_DATE)<Now()+30)) OR (((Company.WC_expiry_date)<Now()+30)) OR (((Company.EL_expiry_date)<Now()+30)) OR (((Company.CAR_expiry_date)<Now()+30));

I am a relative newbie at SQL, so would appreciate it if you assume no prior knowledge.

Thanks

 

[PHV]

Something like this ?
SELECT CompanyName, Count(*) AS NumOfExpiry
FROM Company
WHERE PI_EXPIRY_DATE<Now()+30 OR WC_expiry_date<Now()+30 OR EL_expiry_date<Now()+30 OR CAR_expiry_date<Now()+30
GROUP BY CompanyName

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[masb13]

Thanks PHV.

This is what the SQL statement looks like in my DB:

SELECT Company_name, Count(*) AS NumOfExpiry
FROM Company
WHERE PI_EXPIRY_DATE<Now()+60 OR WC_expiry_date<Now()+60 OR EL_expiry_date<Now()+60 OR CAR_expiry_date<Now()+60
GROUP BY Company_name

It runs and returns the company name, but instead of returning the total number of columns that meet the criteria (should be 3), it returns a value of 1. Any ideas?

 

[PHV]

the total number of columns
???

I need to write a query that counts the number of records

 

[masb13]

Apologies, there was an error in my original description of the problem. It should have said:

... Among the columns are four columns that hold the expiry dates for different types of insurance. I need to write a query that counts the number of these columns, within each record, where the date difference on any of the four columns, between the date now and the expiry date is within the next 30 days...

 

[PHV]

SELECT Company_name, ABS((PI_EXPIRY_DATE<Now()+30)+(WC_expiry_date<Now()+30)+(EL_expiry_date<Now()+30)+(CAR_expiry_date<Now()+30)) AS NumOfExpiry
FROM Company

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[masb13]

Perfect. Thanks a lot PHV.

I also added in the following so that the query doesn't return a record where none of the conditions are met.

WHERE (PI_EXPIRY_DATE<>0) OR (WC_expiry_date<>0) OR (EL_expiry_date<>0) OR (CAR_expiry_date<>0);