Error Division By Zero Postgresql
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings postgresql division by null and policies of this site About Us Learn more about Stack Overflow divide by zero postgresql the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation postgresql ignore division by zero Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it
Postgres Divide By Zero
only takes a minute: Sign up Query giving division by zero error in PostgreSQL up vote 3 down vote favorite I am trying to run the following query which results me postgres error: division by zero select request_count,response_count, (response_count*100) / (request_count+response_count) AS proportion from total_dummy_table; How can I avoid and resolve the division by zero error? Tried to fix using runtime error division by zero the below query but getting the same result as above SELECT request_count, response_count, (response_count*100) / (request_count) AS proportion FROM total_dummy_table ORDER BY (response_count*100) /(CASE request_count WHEN 0 Then NULL ELSE request_count END) DESC NULLS FIRST Please let me know where am I doing wrong, or how can I fix this. Thanks! Result expectation: The query should return me something like below: Request_count | Response_count | Proportion 1603423 | 585706 | 36.52 Quick note: Table total_dummy_table does not have column name proportion that is the addition to the result which have calculated proportion in it. sql postgresql percentage postgresql-9.2 share|improve this question edited Apr 3 '15 at 22:15 A-B-B 8,22333231 asked Mar 5 '14 at 19:40 AKIWEB 4,44933104183 What do you want to display if request_count+response_count is zero ? –Houari Mar 5 '14 at 19:47 If request_count and response count is 0 I want the proportion column should display 0. –AKIWEB Mar 5 '14 at 19:48 Do that at a higher level, see my answer for a potential solution... –hd1 Mar 5 '14
Archives November 2010 September 2010 February 2010 January 2010 December 2009 October 2009 August 2009 Recent Posts Passing a postgresql divide by null variable number of parameters to a function inPostgreSQL Using DISTINCT withCASE Concatenate
Postgresql Nullif
rows with a PostgreSQL aggregatefunction PostgreSQL concat operator doesn't likeNULLs Comment your SQL queries to track down performancehogs!
Postgresql Division
« Outer Joins are faster thanINs Comment your SQL queries to track down performancehogs! » Avoid SQL Divide-by-zero errors by usingNULLIF Posted by jrotenstein on October 6, 2009 I http://stackoverflow.com/questions/22207567/query-giving-division-by-zero-error-in-postgresql picked up this tip while reading SQL Hacks. Let's say you're receiving occasional divide by zero errors: select count(name) / count(*) as "Average" from xyz This will result in a division by error if there are no rows returned. It can be avoided by using the NULLIF function, which returns a null value if the two passed-in expressions are equal: https://sqlbrain.wordpress.com/2009/10/06/avoid-sql-divide-by-zero-errors-by-using-nullif/ select count(name) / NULLIF( count(*), 0) from xyz So, if count(*) = 0, a null value is returned. The resulting division therefore equates to null. While that isn't a particularly useful result, it's better than getting an error! Like this:Like Loading... This entry was posted on October 6, 2009 at 4:24 am and is filed under Clever Queries. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site. Leave a Reply Cancel reply Enter your comment here... Fill in your details below or click an icon to log in: Email (required) (Address never made public) Name (required) Website You are commenting using your WordPress.com account. (LogOut/Change) You are commenting using your Twitter account. (LogOut/Change) You are commenting using your Facebook account. (LogOut/Change) You are commenting using your Google+ account. (LogOut/Change) Cancel Connecting to %s Notify me of new comments via email. « Outer Joins are faster thanINs Comment your SQL queries to track down performancehogs! » Blog at WordPress.com. %d bloggers like this:
pgsql-announce pgsql-bugs pgsql-docs pgsql-general pgsql-interfaces pgsql-jobs pgsql-novice pgsql-performance pgsql-php pgsql-sql pgsql-students Developer lists Regional lists Associations User groups Project lists Inactive lists https://www.postgresql.org/message-id/OF4F1C1C00.5DE7C5DA-ON862569A5.000E2EA5@stsv.seagate.com IRC Local User Groups Featured Users International Sites Propaganda Resources Weekly News Re: Division by zero. From: Fred(dot)Zellinger(at)seagate(dot)com To: pyber(at)street-light(dot)com Cc: pgsql-general(at)postgresql(dot)org Subject: Re: Division by zero. Date: 2000-11-28 02:41:55 Message-ID: OF4F1C1C00.5DE7C5DA-ON862569A5.000E2EA5@stsv.seagate.com (view raw or whole thread) Thread: 2000-11-28 02:41:55 from Fred(dot)Zellinger(at)seagate(dot)com Lists: pgsql-general CASE WHEN expr THEN expr ELSE expr END by zero as in select CASE WHEN baz == 0 THEN null ELSE bar/baz END; See Postgresql documentation, Chapter 5, SQL functions. Warren Vanichuk