CODEWITHSUNDEEP
sqlinformix
Ahmad Alkhawaja
Ahmad Alkhawaja

Reputation: 539

Informix DB - SQL Group By Year, Month, Week, Day, Hour

I want to write sql queries in Informix db to return results that are grouped by Year/Month/Week/Day/Hr (I will write 5 sql queries for each one).

The scenario I have is as follow :

I am familiar with writing sql queries in Informix DB but such scenario turns out to be very complicated to me and I could not figure out how to do it.

Can you please provide me a sample query that accomplish the scenario described above?

Upvotes: 1

Views: 9192

Answers (3)

Hammy
Hammy

Reputation: 86

When I run this query, i get an error: "Error: A syntax error has occurred. (State:37000, Native Code: FFFFFF37)" You have to change the group by to the column number, i.e

select year(resultdate) as resultYear, count(*) as resultCount from caa44340 group by 1

for it to run correctly!

Upvotes: 0

RET
RET

Reputation: 9188

If this isn't exactly what you need, it might get you some way along, or give you some ideas:

SELECT contest_id, YEAR(submission_date) AS submission_period, COUNT(*),
       "Y" AS sub_type
  FROM submissions
  GROUP BY 1, 2
UNION ALL
SELECT contest_id, MONTH(submission_date) AS submission_period, COUNT(*),
       "M" AS sub_type
  FROM submissions
  GROUP BY 1, 2
UNION ALL
SELECT contest_id, DAY(submission_date) AS submission_period, COUNT(*),
       "D" AS sub_type
  FROM submissions
  GROUP BY 1, 2
ORDER BY 1, 4, 2

However, if you're looking at Month by Year instead of Months in isolation, or Week in Year, then I highly recommend you look at creating a "Time Dimension" such as is used in Data Warehousing applications. This is a table with a record for every day that looks a bit like this:

Date         Year   Month     Week      Quarter  Day    MthName
2013-01-01 | 2013 | 2013-01 | 2013W01 | 2013Q1 | Tues | January
2013-01-02 | 2013 | 2013-01 | 2013W01 | 2013Q1 | Wed  | January
..
2013-03-20 | 2013 | 2013-03 | 2013W12 | 2013Q1 | Wed  | March
..
2013-05-01 | 2013 | 2013-05 | 2013W18 | 2013Q2 | Wed  | May

By joining the submission date to this table, you can group by whatever column suits your requirement.

Pro Tip: don't call them what I've labelled those headers, using reserved words will cause you pain :-)

Upvotes: 3

Dan Bracuk
Dan Bracuk

Reputation: 20804

According to the documentation, informix has the functionality to do this. You just don't use the same function for each date component.

Ironically, to get the hour, you use a date function - to_char(). There might be an appropriate mask for the hour. If not, you'll have to use a substring.

For the date components, you would use time components year(), month(), and either day() or weekday(). You might be out of luck for the week.

See these reference pages:

Edit

Here is a simple example, using the year function.

select year(resultdate) resultyear, count(*) results
from etc
group by year(resultdate)

Upvotes: 0

Related Questions