Home > IT Answers > Database > Scalar functions in GROUP BY
Help

Question

  Asked: Dec 30 2004   2:15 PM GMT
  Asked by: sumithar

Scalar functions in GROUP BY


DB2, UDB for mainframe, z/OS, OS/390

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

Subscribe to Alerts! Get questions and answers delivered to your Inbox.


E-mail me updates on this question



   SUBSCRIBE

hidden modal window
Help

Answer Wiki (Improve, edit or add to this answer)

IMPROVE THIS ANSWER
View History
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
Last Answered: Dec 30 2004  6:37 PM GMT  by r937




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
  • AddThis Social Bookmark Button

Browse more Questions and Answers on Database, AS/400 and DataCenter.

Looking for relevant Database Whitepapers? Visit the SearchOracle.com Research Library.


RSS - Discussions Help

Discuss This Answer


You must be logged-in to discuss a question. Log-in/Register

ARWinner  |   Dec 31 2004  3:18AM 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

 

BigKat  |   Jan 2 2005  10:45AM GMT

give the colums names and group by the column not the function.

select …. AS NAME …. group by NAME….

 

Stanton  |   Jan 3 2005  10:48AM 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