Group weekly over several years (postgresql)

Question

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:

It should be something like:

How can I identify which year a week belongs to?

Any tip or clue welcome.

TIA,

score 4 · Accepted Answer · 2016-05-30T11:38:43.380

4

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

edited May 30 '16 at 11:38

answered May 30 '16 at 08:23

Right on track! Changed GROUP BY to 'iyyy-iw' and it worked as expected. Thank you! – sibert May 30 '16 at 11:23

sagi · Answer 2 · 2016-05-30T08:20:42.273

1

Just concat the year with the weak :

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

Edit: You can try to replace the first column select with this :

concat(date_part('year' ,date),
       CASE WHEN date_part('week' ,date) < 10 THEN '0' ELSE '' END,
       date_part('week' ,date))

edited May 30 '16 at 08:20

answered May 30 '16 at 08:05

sagi

40,026
6
59
84

On the right track! But the sort order remains wrong because the '0' is missing. 20161 comes before 201553. Any tip? – sibert May 30 '16 at 08:14
@sibert Edited the answer – sagi May 30 '16 at 08:20

Group weekly over several years (postgresql)

2 Answers2