Scalar functions in GROUP BY

Sumithar

pts.

Tags:

IBM DB2

OS/390

UDB for mainframe

z/OS

Is there a reason why I cannot use a SCALAR function in a Group BY clause? I have a table with a column that is of type timestamp. I want to get a count of all dates for which there are multiple rows. So I devise a query of the sort SELECT COUNT(*), DAYS(TMSTP_COL) FROM MYTAB A GROUP BY DAYS(TMSTP_COL) HAVING COUNT(*) > 1 ORDER BY COUNT(*) DESC I get an error DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "(". I know it is complaining about the DAYS function since the moment I remove it, the error goes away Thanks

Asked: December 30, 2004 2:15 PM Last updated: October 21, 2009 1:33 AM

Answer Wiki

Last updated: December 30, 2004 6:37 PM GMT

0 pts.
History
Contributors
- Ordered by most recent
- 0 pts.

Thanks. We'll let you know when a new response is added.

this is db2, right?

no idea why it isn’t working, it looks okay to me, but i don’t have db2 myself, so i can’t test this

select thecount
, days(TMSTP_COL) as days_timestmp
from (SELECT COUNT(*) as thecount
, TMSTP_COL
FROM MYTAB A
GROUP BY TMSTP_COL
HAVING COUNT(*) > 1) as dt
order by thecount desc

Discuss This Question: 5 Replies

ARWinner Dec 31, 2004 3:18 AM GMT

The ORDER BY clause is for sorting--after the result set is selected. That is why the function is not allowed, it is sorta meaningless to count again. Try this: SELECT COUNT(*) as quantity, DAYS(TMSTP_COL) FROM MYTAB A GROUP BY DAYS(TMSTP_COL) HAVING COUNT(*) > 1 ORDER BY quantity DESC or this: SELECT COUNT(*), DAYS(TMSTP_COL) FROM MYTAB A GROUP BY DAYS(TMSTP_COL) HAVING COUNT(*) > 1 ORDER BY 1 DESC HTH Andy

0 pointsBadges:

report
BigKat Jan 2, 2005 10:45 AM GMT

Give the columns names and group by the column not the function. select .... AS NAME .... group by NAME....

9,110 pointsBadges:

report
Stanton Jan 3, 2005 10:48 AM GMT

Here is what you have to do, I've tested it to make sure it works. select count(*) THECOUNT, X.M_DAYS FROM (SELECT DAYS(TMSTP_COL) M_DAYS FROM MYTAB) X GROUP BY X.M_DAYS HAVING COUNT(*) > 1 ORDER BY THECOUNT DESC Stanton

0 pointsBadges:

report
TomLiotta Oct 19, 2009 8:58 PM GMT
I've always given my derived columns names because I've never worked out the rules. The SQL Reference (V5R3) has this to say: If GROUP BY or HAVING is used:
- Each column-name in the select list must identify a grouping expression or be specified within a column function:
  - – If the grouping expression is a column name, the select list may apply additional operators to the column name. For example, if the grouping expression is a column C1, the select list may contain C1+1.
  - – If the grouping expression is not a column name, the select list may not apply additional operators to the expression. For example, if the grouping expression is C1+1, the select list may contain C1+1, but not (C1+1)/8.
That last bit -- "If the grouping expression is not a column name" -- sure makes it sound like a column name is not required. And the example -- "the grouping expression is C1+1" -- sure seems to show that a column name isn't required. But nothing is said about a SCALAR function either way. I've never figured out the exact rules, so I just do like others have suggested -- [DAYS(TMSTP_COL) as NewCol] -- in the SELECT list and reference [NewCol] elsewhere. Tom
125,585 pointsBadges:
report
TomLiotta Oct 21, 2009 1:33 AM GMT
And as a test, this works in V5R3:
```
SELECT
   count(*),days(SYSLCHG)
 FROM mytesttbl
   GROUP BY days(SYSLCHG)
   HAVING count(*)<>0
```
As does this:
```
SELECT
   count(*),days(SYSLCHG)
 FROM mytesttbl
   GROUP BY days(SYSLCHG)
   HAVING count(*)<>0
   ORDER BY  1  desc
```
The first discussion point by ARWinner is on the mark. Of course, that assumes that TMSTP_COL is a timestamp. Tom
125,585 pointsBadges:
report