-1

When running my query I receive a result set which I would like to aggregate and show in two different columns. This result set itself is derived from different aggregate functions and window functions.

Here is my query

select user_created, 
       extract(day from creation_date) as date, 
       min(balance_cents/100::float) as balance,
       extract(day from creation_date)-lag(extract(day from creation_date),1) 
                                       OVER (partition by user_created) as diff,
       case when (lag(min(balance_cents/100::float)) over (partition by user_created))>0 
            then (extract(day from creation_date)-lag(extract(day from creation_date),1) 
                                                  OVER (partition by user_created))
                                                  *(lag(min(balance_cents/100::float)) 
                                                    over (partition by user_created))
                                                  *0.004/360
            else (extract(day from creation_date)-lag(extract(day from creation_date),1) 
                                                  OVER (partition by user_created))
                                                  *(lag(min(balance_cents/100::float)) 
                                                  over (partition by user_created))
                                                  *0.0849/360 
            END wish_to_sum
from     table
where    creation_date>'2017-06-01' and user_created in ('1')
group by 1,2
ORDER BY 2

And the result set is this table

user_created    date    balance day_diff    wish_to_sum
1                  1    404.66  null          null
1                  3    392.41  2             0.008992444
1                  7    295.41  4             0.017440444
1                  10   275.41  3             0.009847
1                  13   155.24  3             0.009180333
1                  15   151.74  2             0.003449778
1                  20   11.15   5             0.00843
1                  21   -5.15   1             0.000123889
1                  23   31.15   2            -0.002429083

I would like to sum the last column by summing the positive and negative values separately and display them in two columns.So, my expected result is

user_created            Pos            Neg
10/24/2014 18:51    0.057463889    -0.002429083

When trying to remove the 2,3,4 columns and aggregate the wish_to_sum column, it displays an error message saying that SUM function cannot be used with window function.

How can I aggregate my results?

  • can you create a simplified table with simplified data and just show us your desired output. – Evan Carroll Jun 23 '17 at 18:33
1

I can't say for sure, but when you removed the fields from the projection, did you alter the group by to only the first column?

Also, I guess you could do something like this:

SELECT
  USER_CREATED,
  SUM(CASE WHEN WISH_TO_SUM >= 0 THEN WISH_TO_SUM ELSE 0 END) AS POS,
  SUM(CASE WHEN WISH_TO_SUM < 0 THEN WISH_TO_SUM ELSE 0 END) AS NEG
FROM (
  SELECT
    USER_CREATED,
    CASE
      WHEN (LAG(MIN(BALANCE_CENTS / 100 ::FLOAT)) OVER(PARTITION BY USER_CREATED)) > 0 THEN
        (EXTRACT(DAY FROM CREATION_DATE) - LAG(EXTRACT(DAY FROM CREATION_DATE), 1) OVER(PARTITION BY USER_CREATED)) * (LAG(MIN(BALANCE_CENTS / 100 ::FLOAT)) OVER(PARTITION BY USER_CREATED)) * 0.004 / 360
      ELSE (EXTRACT(DAY FROM CREATION_DATE) - LAG(EXTRACT(DAY FROM CREATION_DATE), 1) OVER(PARTITION BY USER_CREATED)) * (LAG(MIN(BALANCE_CENTS / 100 ::FLOAT)) OVER(PARTITION BY USER_CREATED)) * 0.0849 / 360
    END WISH_TO_SUM
  FROM TABLE
  WHERE CREATION_DATE > '2017-06-01'
    AND USER_CREATED IN ('1')
)
GROUP BY 1  

If that doesn't work on the fly I'm sorry. I am not used to work with Postgress and couldn't test the code.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.