PostgreSQL- sum not recognized as aggregate function? -


i need have running amount of transactions per year. if total amount @ 2015 150 , @ 2016, total amount 90. means @ 2016 running amount 240. , on.

so have data:

create table transactions2(year_num int, amount int);  insert transactions2 values(2015, 100); insert transactions2 values(2015, 50); insert transactions2 values(2016, 90); insert transactions2 values(2017, 100); insert transactions2 values(2019, 200);  select year_num, count(amount), sum(amount)  on (order year_num)  transactions2  group year_num order year_num;

if run select sql, get:

error:  column "transactions2.amount" must appear in group clause or used in aggregate function line 9: select year_num, count(amount), sum(amount) on (order y...                                             ^  ********** error **********  error: column "transactions2.amount" must appear in group clause or used in aggregate function sql state: 42803 character: 328

but have amount in sum function. why not working? if wrap sum(count(sum)), works, not need have sum of count, need sum.

do need write inner select this?

in expression:

sum(amount) on (order year_num)

sum() not simple aggregate, it's window function.

you want use both count() , sum() window functions:

select distinct year_num, count(amount) on w, sum(amount) on w transactions2  window w (order year_num) order year_num;   year_num | count | sum  ----------+-------+-----      2015 |     2 | 150      2016 |     3 | 240      2017 |     4 | 340      2019 |     5 | 540 (4 rows)

Comments

Post a Comment

Popular posts from this blog

javascript - Clear button on addentry page doesn't work -

this addentry blog page have made scratch , there underlying problems code particularly clear button supposed clear text entered when button clicked on doesn't work have saved html file problem. also 1 other problem submit button works doesn't pass of text index file have written php , reason whenever click on submit button redirects me index page without posting entry have made. <!doctype html> <html> <head> <script> function message() { alert("clear form?"); } </script> <style> body {background-color: beige;} h1 {color: black;} p {color: black;} textarea { width: 50%; height: 150px; padding: 12px 20px; box-sizing: border-box; border: 2px solid #ccc; border-radius: 4px; background-color: white; font-size: 16px; resize: none; } div { padding-left: 20px; } </style> </head> <meta charset="utf-8"> <link rel="stylesheet" href="....
Read more

python - Error: Unresolved reference 'selenium' What is the reason? -

# -*- coding: utf-8 -*- selenium import selenium import unittest, time, re class rc(unittest.testcase): def setup(self): self.verificationerrors = [] self.selenium = selenium("localhost", 4444, "*chrome", "http://stackoverflow.com/") self.selenium.start() def test_rc(self): sel = self.selenium sel.open("/") sel.type("id=kw", "selenium") sel.click("id=container") sel.click("id=su") def teardown(self): self.selenium.stop() self.assertequal([], self.verificationerrors) if __name__ == "__main__": unittest.main() this recorded selenium ide, selenium remote control code. in ide test passed.   in pycharm open, first line ( from selenium import selenium ) there error: unresolved reference 'selenium' what reason? my environment is python3.5 selenium3.3.1 selenium-server-standalone-...
Read more

ios - Pass NSDictionary from Javascript to Objective-c in JavascriptCore -

i'm using javascriptcore in app. now, i'm passing variables jscontext can passed objective-c. however, 1 of variables, nsdictionary, not passing through correctly. run code below: var evaluate = function(variables) { app.setdictionary(variables.dictionary); } this simple example has these following methods set in jscontext . this setdictionary() method: - (void)setdicationary:(nsdictionary *)dictionary { self.mutabledictionary = [dictionary mutablecopy]; } this variables.dictionary : - (nsdictionary *)dictionary { return self.values; } and how call evaluate() : jsvalue *jsfunction = self.context[@"evaluate"]; jsvalue *value = [jsfunction callwitharguments:@[self.variables]]; however, in setdictionary method, don't nsdictionary, instead nsstring containing [object object] . any ideas how can solve this? although javascriptcore automatically converts types between objective-c or swift , javascript, suggest implement ex...
Read more