CODEWITHSUNDEEP
db2
Mike
Mike

Reputation: 2900

How do I order my query by a field and still group by a subset of that field in db2?

Sorry if the title is confusing. Here is the query I have

Select MONTH(DATE(TIMESTAMP)), SUM(FIELD1), SUM(FIELD2) from TABLE WHERE TIMESTAMP BETWEEN '2009-07-26 00:00:00' AND '2010-02-24 23:59:59' GROUP BY MONTH(DATE(TIMESTAMP))

This will let me get the month number out of the query. The problem is that right now it is sorting the months 1,2,3,4.... when it spans two separate years. I need to be able to sort this query by year then month.

If I add "ORDER BY TIMESTAMP" at the end of my query I get this error:

Column TIMESTAMP or expression in SELECT list not valid. SQLCODE=-122

Also I changed the field names for this question to keep it clear the field isn't actually called TIMESTAMP

Upvotes: 1

Views: 496

Answers (1)

JonH
JonH

Reputation: 33143

You need to group by year then month.:

SELECT YEAR(YourField),
Month(YourField),
SUM(Field1),
SUM(Field2)
FROM Table
WHERE...
GROUP BY
YEAR(YourField),
Month(YourField)
ORDER BY
YEAR(YourField),
Month(YourField)

Upvotes: 2

Related Questions