output with different rows from different columns from the same SQL table

Question

I'm trying to calculate running averages of past 4th month. So I need to get the 4th value of each month

month_date | Month 1 | Month 2 | Month 3| Month 4
---------------------------------------------
 11   |   0    |   0     |   0    |   0
 10   |   2    |   0     |   0    |   0
 09   |   3    |   4     |   0    |   0
 08   |   8    |   7     |   9    |   0
 07   |   6    |   8     |   11   |   5
 06   |   3    |   4     |   0    |   8
 05   |   8    |   7     |   9    |   9
 04   |   6    |   8     |   11   |   5

[Expected Output]

 | Month 1 | Month 2 | Month 3| Month 4
----------------------------------------
  |   6     |   4     |   9    |   5

What I tried to do

I tried to rank excluding zeros like row_number over (order by month desc) - that didn't work

-I tried to to use NULLS LAST function that didn't work either because I need to order based on month not on each month

Please help

This structure appears sub-optimal. A data base table is not a spreadsheet — Strawberry, Dec 03 '19 at 21:53
Column in expected output A doesn't match to others. There is `8`, shouldn't be `6`? — Skamielina, Dec 03 '19 at 21:54

Gordon Linoff · Answer 1 · 2019-12-04T22:48:36.340

1

This is a very strange requirement. But you can do this using a window function to get the first non-zero rank. Then add three and use conditional aggregation:

select max(case when rank = month1_rank0 + 3 then month1 end) as month1,
       max(case when rank = month2_rank0 + 3 then month2 end) as month2,
       max(case when rank = month3_rank0 + 3 then month3 end) as month3,
       max(case when rank = month4_rank0 + 3 then month4 end) as month4       
from (select t.*,
             min(case when month1 <> 0 then rank end) over () as month1_rank0,
             min(case when month2 <> 0 then rank end) over ()  as month2_rank0,
             min(case when month3 <> 0 then rank end) over ()  as month3_rank0,
             min(case when month4 <> 0 then rank end) over ()  as month4_rank0
      from t
     ) t

edited Dec 04 '19 at 22:48

answered Dec 03 '19 at 21:35

Gordon Linoff

1,242,037
58
646
786

I tried this but this doesn't work because you need to group by , if you do group by -it changes everything . – dbA Dec 04 '19 at 06:57
@dba . . . Your sample result set has only one row; hence, no `group by` is appropriate. If you have a different question, then ask that as a *new* question with appropriate sample data and desired results. – Gordon Linoff Dec 04 '19 at 11:36
In your sub-query since you are doing t.* - min() function - so subsequent needs a group by. But really appreciate your efforts for trying. – dbA Dec 04 '19 at 13:17
@dbA . . . I think I typed that without thinking. It should not be there. – Gordon Linoff Dec 04 '19 at 13:41
I think it should be , if not we wouldn't be able to retrieve month/2/3/4 values from the subquery, if the subquery just has the min(rank) number – dbA Dec 04 '19 at 14:16
@dbA . . . I wanted window functions. – Gordon Linoff Dec 04 '19 at 22:48

output with different rows from different columns from the same SQL table

1 Answers1