SOLVED

Inserting empty rows

Copper Contributor

This should be simple, but my SQL skills are rusty.  I have a query that extracts the year and a count of incidents from  a table.

SELECT year, Count(year) AS incidents
FROM Table2
WHERE ((Table2.cause_ID=15) OR (Table2.cause_ID=16))
GROUP BY year;

 

Of course, many years have no matching cause_ID = 15 or 16.  I would like an output of year and 0 for those years.

20 Replies

@Clayton_cramer 

If you want to see all years regardless of whether there are any matching cause_IDs for those years, then you can't use the WHERE clause to exclude them.

Instead, you need to use a conditional for the Count(year) to return the actual Count where there are matching cause_IDs or 0 where there are none.

 

SELECT year, Iif([cause_ID] = 15 or [cause_ID]=16), count(year), 0) AS incidents

FROM Table2

GROUP BY year;

wrong number of arguments used in query function lif([cause_ID) = 15 or [cause_ID]=16'.

@Clayton_cramerlif looks like a typo for if, but "undefined function 'if' in expression."

@Clayton_cramer 

 

Iif() is the immediate If function; that's how it is spelled.

 

There must be a different syntax error in my suggested approach. It's aircode since I don't have visibility to your actual table(s). See if you can suss it out, but post back if not.

@George Hepworth 

Wait, there may be a missing left paren. Add a second one after the first one  

@George Hepworth  I added that parenthesis and now I get "Your query does not include the specified expression 'lif([cause_ID]15 Or [cause_ID=16,count(year),0)' as part of an aggregate function.

 

SELECT year, Iif(([cause_ID] = 15 or [cause_ID]=16), count(year), 0) AS incidents

FROM Table2

GROUP BY year;

 

I would upload the database, but it is huge: every mass murder in American history from 1657 to 1914 (so far).  year is a number, cause_ID is a number.

@Clayton_cramer 

 

Reread what you entered, please.  What you posted here is now missing a closing square bracket. It's also missing an equal sign. Trouble-shooting often requires taking a closer, second look at things.

I put everything in a format to simplify matching parens and brackets

SELECT year, iif (
  (
    (
    [cause_ID] = 15
    )
  or
    (
    [cause_ID]=16
    )
  )
,
count(year), 0) -- matches opening ( of iif
AS incidents

FROM Table2

GROUP BY year;

 

All parens and brackets match.  "Your query does not include the specified expression... as part of an aggregate function."  I put the [cause_ID] clauses in their own parens to make sure iif was treating that as a single boolean operation.  Even removing those parens, I get the same error message.

@Clayton_cramer 

 

That's a different error. Let's try adding the expression to the GROUP BY.

Wrong approach, sorry. See the next post.

@Clayton_cramer 


And my apologies. I didn't try it out before I sent it and I think this will actually give you the result.


SELECT year, Sum(IIF([cause_ID] = 15 or [cause_ID]=16) , 0,1) AS incidents

FROM Table2

GROUP BY year

 

That avoids the complication of having the Aggregate in either the GROUP BY or in the SELECT. 

@George Hepworth"Wrong number of arguments used with function in query expression" followeed by the Sum(IIF expression.  Shouldn't Sum only take one expression?  It appears as if the IIF... 0,1 are being treated as three arguments to SUM.

@Clayton_cramer 

 

I've always believed in the power of "trial and error" as a way to resolve such problems.

What happens when you change the suggested approach the way you describe?

IIF needs an expression and two values for TRUE and FALSE evaluations of the expression.  It should return a single VALUE and SUM should add all those values for all rows.  What you provided makes perfect sense--except to Access, which is still complaining about wrong number of arguments.  It is almost like IIF does not recognize that expression.

@Clayton_cramer If you can, I would like some sample data to work with in order to see what might the problem is. Thanks.

BTW.  Access most definitely will evaluate the expression once it is properly written, so please include your query with that expression with the sample data. Thanks.

I am embarrassed to say that I cannot figure out how to export my database with just a few records in it, so it is a size that I can upload.

@George HepworthI tried attaching the database:

Correct the highlighted errors and try again.

  • The attachment's massmurder.accdb content type (application/msaccess) does not match its file extension and has been removed.

@Clayton_cramer If you can, compress it into a ZIP file and see if that will upload. If not, we can try an alternate method.

best response confirmed by Clayton_cramer (Copper Contributor)
Solution

@Clayton_cramer If you don't like IIF, then use IN:

SELECT 
    year, 
    Abs(Sum([cause_ID] In (15, 16))) AS incidents
FROM 
    Table2
GROUP BY 
    year;

@Gustav Brockperfect!

1 best response

Accepted Solutions
best response confirmed by Clayton_cramer (Copper Contributor)
Solution

@Clayton_cramer If you don't like IIF, then use IN:

SELECT 
    year, 
    Abs(Sum([cause_ID] In (15, 16))) AS incidents
FROM 
    Table2
GROUP BY 
    year;

View solution in original post