Trouble in calculating the field while creating view in postgresql -

-

i have 2 tables q1data , q1lookup in postgres database. q1data contains 3 columns (postid, reasonid, other) , q1lookup contains 2 columns (reasonid, reason).

i trying create view include 4 columns (reasonid, reason, count, percentage). count count of each reason , percentage should each count divided total of count(*) q1data (i.e. total rows if reasonid).

but gives error , says syntax error near count(*). following code using. please help.

select       cwfis_web.q1data.reasonid reasonid,      cwfis_web.q1lookup.reason reason,      count(cwfis_web.q1data.reasonid) count,      round(         (             (                 count(cwfis_web.q1data.reasonid)                  /                  (select count(0) count(*) cwfis_web.q1data)             ) * 100         )      ,0) percentage        cwfis_web.q1data  join       cwfis_web.q1lookup       on cwfis_web.q1data.reasonid = cwfis_web.q1lookup.reasonid  group       cwfis_web.q1data.reasonid;

firstly, have invalid piece of syntax there: count(0) count(*). replacing plain count(*), , adding missing group by entry reason, gives this:

select       cwfis_web.q1data.reasonid reasonid,      cwfis_web.q1lookup.reason reason,      count(cwfis_web.q1data.reasonid) count,      round(         (             (                 count(cwfis_web.q1data.reasonid)                  /                  (select count(*) cwfis_web.q1data)             ) * 100         )      ,0) percentage        cwfis_web.q1data  join       cwfis_web.q1lookup       on cwfis_web.q1data.reasonid = cwfis_web.q1lookup.reasonid  group       cwfis_web.q1data.reasonid,      cwfis_web.q1lookup.reason;

however, as live demo shows doesn't give right value percentage, because count(cwfis_web.q1data.reasonid) , (select count(*) cwfis_web.q1data) both of type integer, integer division performed, , result truncated 0.

if cast these numeric (the expected argument type of the 2-parameter round() function, this:

select       cwfis_web.q1data.reasonid reasonid,      cwfis_web.q1lookup.reason reason,      count(cwfis_web.q1data.reasonid) count,      round(         (             (                 count(cwfis_web.q1data.reasonid)::numeric                 /                  (select count(*) cwfis_web.q1data)::numeric             ) * 100         )      ,0) percentage        cwfis_web.q1data  join       cwfis_web.q1lookup       on cwfis_web.q1data.reasonid = cwfis_web.q1lookup.reasonid  group       cwfis_web.q1data.reasonid,      cwfis_web.q1lookup.reason;

which this live demo shows gives more hoping for. (alternatively, can cast float, , lose ,0 argument round(), as in demo.)


Comments

Post a Comment

Popular posts from this blog

Need help in packaging app using TideSDK on Windows -

-
i developed application using tidesdk on windows , when click launchapp works fine. moved package , installed imagemagick , wix 3.0 required packaging on windows. clicked on package runtime , cannot guess packaged app is? found file 'installer' in workspace when click on it says installer not find application path . think application not being packaged properly. here log see in tidesdk: preparing package desktop app runtime. 1 moment... staging trial -> copying contents d:\lab\tidesdk\trial d:\lab\tidesdk\trial\packages\win32\bundle\trial -> copying installer c:\programdata\tidesdk\sdk\win32\1.3.1-beta\installer d:\lab\tidesdk\trial\packages\win32\bundle\trial -> copying runtime d:\lab\tidesdk\trial\packages\win32\bundle\trial -> date: d:\lab\tidesdk\trial\packages\win32\bundle\trial\modules\app\1.3.1-beta -> date: d:\lab\tidesdk\trial\packages\win32\bundle\trial\modules\codec\1.3.1-beta -> date: d:\lab\tidesdk\trial\packages\win32\bundle\trial\mod...
Read more

java - Why does my date parsing return a weird date? -

-
here code using: string string = "08/07/2013".replace('/', '-'); date date = new simpledateformat("yyyy-mm-dd").parse(string); why date return: "wen jan 3 00:00:00 est 14"? not @ date format told use. edit: need format because database using requires format. the format use parse date string, not match it. using yyyy 08 . use following format: new simpledateformat("dd-mm-yyyy") and why @ replacing / - ? can build pattern original string only: string string = "08/07/2013" date date = new simpledateformat("dd/mm/yyyy").parse(string); and if want date string in yyyy-mm-dd format, can format date using dateformat#format(date) method: string formatteddate = new simpledateformat("yyyy-mm-dd").format(date); see also: change date format in java string simpledateformat java doc
Read more