Divide Two Counts From One Select

I have a table like this: date(timestamp) Error(integer) someOtherColumns I have a query to select all the rows for specific date: SELECT * from table WHERE date::date = '2010

Solution 1:

what about this (if error could be only 1 and 0):

selectdate,
   sum(Error)::numeric / count(Error) as"Percentage of failure"from Table1
groupbydate

or, if error could be any integer:

selectdate,
   sum(casewhenError > 0then1end)::numeric / count(Error) as"Percentage of failure"from Table1
groupbydate

---

Just fount that I've counted not 0 (assumed that error is when Error != 0), and didn't take nulls into accounts (don't know how do you want to treat it). So here's another query which treats nulls as 0 and counts percentage of failure in two opposite ways:

selectdate,
    round(count(nullif(Error, 0)) /count(*) ::numeric , 2) as "Percentage of failure",
    1- round(count(nullif(Error, 0)) /count(*) ::numeric , 2) as "Percentage of failure2"
from Table1
groupbydateorderbydate;

sql fiddle demo

Solution 2:

try this

selectcast(data1.count1 asfloat)/cast(data2.count2 asfloat) 
 from (
selectcount(*) as count1 fromtabledate::date='2010-01-17'and Error =0) data1, 

(selectcount(*) as count1 fromtabledate::date='2010-01-17') data2

Solution 3:

SELECTdate
     , round(count((error =0) ORNULL) /count(*)::numeric, 2) AS percent_fail
FROM   tbl
GROUPBY1ORDERBY1;

This even works if error can be NULL.

-> SQLfiddle demo.

Much more (incl. implications on performance) under this closely related question: Compute percents from SUM() in the same SELECT sql query

Comparison and benchmark of ways to count in this related answer on dba.SE.

Solution 4:

You can use generate_series and takes it from there.

Like this:

WITH CTE AS 
(
     SELECT 
         m
        --,extract('year'  FROM m) AS theyear--,extract('month' FROM m) AS themonth--,extract('day' FROM m) AS theday

        ,(SELECTCOUNT(*) AS cnt FROMtableWHEREdate::date= m AND Error =1) AS data1 
        ,(SELECTCOUNT(*) AS cnt FROMtableWHEREdate::date= m) AS data2 
    FROM  
    (
        SELECT generate_series('2012-04-01'::date, '2016-01-01'::date, interval'1 day') AS m
    ) AS g 
) -- END OF CTE SELECT 
      m
     ,COALESCE(data1 *100.0/NULLIF(data2, 0.0), 0.0) AS ErrorPercentage
FROM CTE

See this for details: How to perform a select query in a DO block?