SQL column reference is invalid

Question

I am using Jaspersoft's iReport to create a report that will pull data from my Maintenance Assistant CMMS database. The DB is on the localhost, and I am not creating any tables or columns. MA CMMS takes care of that. I only want to pull the data to arrange in a report.

Here is my code:

SELECT * 
FROM   "tblworkordertask" 
WHERE  "dbltimespenthours" > 0 
       AND "dtmdatecompleted" BETWEEN $P{DATE_FROM} AND $P{DATE_TO}
GROUP  BY "intworkorderid" 

and my error:

Caused by: java.sql.SQLSyntaxErrorException: Column reference 'tblWorkOrderTask.id' is invalid, or is part of an invalid expression.  For a SELECT list with a GROUP BY, the columns and expressions being selected may only contain valid grouping expressions and valid aggregate expressions.

I don't know why the error is referring to 'tblWorkOrderTask.id' because I don't have such a column, nor did I ask for that column.

If I take out the group by clause, it works fine, but as you could expect, I get multiple results with the same WorkOrderID. I want to group it by this column, and then count the results. I tried using SELECT DISTINCT, but then I get errors about columns that aren't selected.

Answer 1

You're selecting all columns in the tblWorkOrderTask table. The "id" column is the first column in that table. You are getting an error because you do not have all columns specified in the select list.

This select would work, but I'm not sure what information you need out of your table.

SELECT id, intworkorderid
FROM tblWorkOrderTask 
group by id, intworkorderid

http://www.w3schools.com/sql/sql_groupby.asp

Answer 2

Get rid of the GROUP BY clause -- if you're just trying to order the result, then use ORDER BY instead; but otherwise, you don't need either.

EDIT

As the error says, everythign in your SELECT list must be one of two things -- either 1) also listed in your GROUP BY list, or 2) an aggregated value. Here is a sample that will work:

SELECT intworkorderid, COUNT(*) 
FROM   "tblworkordertask" 
WHERE  "dbltimespenthours" > 0 
       AND "dtmdatecompleted" BETWEEN $P{DATE_FROM} AND $P{DATE_TO}
GROUP  BY "intworkorderid" 

Answer 3

Yes - in order to use group by, you need to be specific in the select line.

So first, decide which fields you want to display. If you want them all, then include them all.

As soon as you add a COUNT() function to get a count of the selected fields, you will need to add the GROUP BY clause. COUNT() is an AGGREGATE function, like SUM() and AVG().

It's a little counter-intuitive and a bit of a pain to specify so many fields in the GROUP BY clause, but it's necessary. The FIRST GROUP BY field is the most important, since this is usually what you are concerned about. This first field can be any of the SELECTed fields, it is not necessarily the first.

Include EVERY field in your GROUP BY that is not an AGGREGATE function like COUNT().

Also, if you are trying to COUNT a group of orders, you probably don't want or need all of the fields in the SELECT. You probably want to specify just the fields that are unique to the work order ID.

Example: If you want to get a COUNT of these fields, you would specify all of the SELECTED fields EXCEPT the COUNT().

SELECT
    intWorkOrderID,
    COUNT(id),
    strDescription
FROM   tblworkordertask
WHERE  dbltimespenthours > 0 
       AND dtmdatecompleted BETWEEN $P{DATE_FROM} AND $P{DATE_TO}
GROUP BY
    intworkorderid,
    strDescription