sibert sibert - 6 months ago 15
SQL Question

Group weekly over several years (postgresql)

This query groups per week correctly, but sorts strange:

SELECT date_part('week' ,date), SUM(qty)
FROM hr
WHERE date between '2016-01-01' and '2016-01-31'
GROUP BY date_part('week', date)
ORDER BY 1


The result of this query is:

wrong sort

It should be something like:

correct sort

How can I identify which year a week belongs to?

Any tip or clue welcome.

TIA,

Answer

Use to_char() to format the year and week in a single sortable value:

SELECT to_char(date '2016-01-01', 'iyyy-iw'), SUM(qty)
FROM hr
WHERE date between '2016-01-01' and '2016-01-31'
GROUP BY to_char(date '2016-01-01', 'iyyy-iw')
ORDER BY 1

The format mask iyyy and iw use the ISO week numbering which you seem to be expecting (it e.g. assigns 2015-53 for 2016-01-01). If you don't want that you need to switch to yyyy and ww instead