Divide two counts from one select

Question

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-01-17'

Now I need to count all rows which Error is equal to 0(from that day) and divide it by count of all rows(from that day).

So result should look like this

Date(timestamp)      Percentage of failure
2010-01-17           0.30

Database is pretty big, millions of rows..

And it would be great if someone know how to do this for more days - interval from one day to another.

Date(timestamp)      Percentage of failure
2010-01-17           0.30
2010-01-18           0.71
and so on

BTW Do not use reserved words like `date` for column names. It can cause some nasty bugs in queries. — Ihor Romanchenko, Oct 21 '13 at 14:10
Can `error` be `NULL`? Do you need a row in the result for days with no entry in the table at all? — Erwin Brandstetter, Oct 21 '13 at 17:09

Roman Pekar · Accepted Answer · 2013-10-22T05:54:17.623

6

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

select
   date,
   sum(Error)::numeric / count(Error) as "Percentage of failure"
from Table1
group by date

or, if error could be any integer:

select
   date,
   sum(case when Error > 0 then 1 end)::numeric / count(Error) as "Percentage of failure"
from Table1
group by date

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:

select
    date,
    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
group by date
order by date;

sql fiddle demo

edited Oct 22 '13 at 05:54

answered Oct 21 '13 at 14:02

Roman Pekar

107,110
28
195
197

@Roman Pekar: This will (or at least should) fail with division by 0 if count(*) is 0... – Stefan Steiger Oct 23 '13 at 11:29
@Quandary well.. don't think any SQL query could return record with count(*) = 0 :) – Roman Pekar Oct 23 '13 at 11:34
@Roman Pekar: Ah yea, my fault, you're right, group by saves you there by just omiting the days of no activity. – Stefan Steiger Oct 23 '13 at 11:45

score 3 · Answer 2 · answered Oct 21 '13 at 13:02

3

try this

select cast(data1.count1 as float)/ cast(data2.count2 as float) 
 from (
select count(*) as count1 from table date::date = '2010-01-17' and Error = 0) data1, 

(select count(*) as count1 from table date::date = '2010-01-17') data2

answered Oct 21 '13 at 13:02

Apostolos

10,033
5
24
39

score 1 · Answer 3 · edited May 23 '17 at 11:54

1

SELECT date
     , round(count((error = 0) OR NULL) / count(*)::numeric, 2) AS percent_fail
FROM   tbl
GROUP  BY 1
ORDER  BY 1;

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.

edited May 23 '17 at 11:54

Community

1
1

answered Oct 21 '13 at 17:07

Erwin Brandstetter

605,456
145
1,078
1,228

@quin61: Be sure to follow the links for information on performance. `count(*)` is a bit faster than `count(col)` and also safe against NULL values. Strictly speaking, Roman's answer is incorrect as long we don't know whether the column can be NULL. – Erwin Brandstetter Oct 21 '13 at 17:46

score 0 · Answer 4 · edited May 23 '17 at 11:54

0

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

        ,(SELECT COUNT(*) AS cnt FROM  table WHERE date::date = m AND Error = 1) AS data1 
        ,(SELECT COUNT(*) AS cnt FROM  table WHERE date::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?

edited May 23 '17 at 11:54

Community

1
1

answered Oct 21 '13 at 12:58

Stefan Steiger

78,642
66
377
442

It's still giving me: ERROR: syntax error at or near "WITH" LINE 1: WITH CTE AS – quin16 Oct 21 '13 at 13:29
@quin61: I don't know what your table and date field is, maybe a invalid date, but that statement works fine on my machine. – Stefan Steiger Oct 23 '13 at 11:35

Divide two counts from one select

4 Answers4

Linked