Home postgresql: how to avoid receiving boolen answer from select query
Reply: 1

postgresql: how to avoid receiving boolen answer from select query

Mai Mahmoud
1#
Mai Mahmoud Published in 2018-02-14 01:10:44Z

I was trying to do some calculations in the select statement and my code was

SELECT DATE (TIME), 
round(100.0 * COUNT(*) FILTER(WHERE STATUS LIKE '404%') / COUNT(log.STATUS), 2) AS errors
FROM log
GROUP BY DATE (TIME)
ORDER BY errors DESC;

and the output was fine

date | errors -----------+------- 2016-07-17 | 2.26 2016-07-19 | 0.78 2016-07-24 | 0.78 2016-07-06 | 0.77

but when I try to change the code to this sentence

SELECT DATE (TIME), 
(100.0 * COUNT(*) FILTER(WHERE STATUS LIKE '404%') / COUNT(log.STATUS) > 1) AS errors
FROM log
GROUP BY DATE (TIME)
ORDER BY errors DESC; 

I receive a Boolean output and i want the output to show only the first row

date | errors ------------+-------- 2016-07-17 | t 2016-07-02 | f 2016-07-03 | f 2016-07-04 | f

Kaushik Nayak
2#
Kaushik Nayak Reply to 2018-02-14 04:40:25Z

you could use a WHERE clause over and above your result to get the > 1 records.

SELECT "Date"
    ,errors
FROM (
    SELECT DATE (TIME) AS "Date"
        ,round(100.0 * COUNT(*) FILTER(WHERE STATUS LIKE '404%') 
        / COUNT(log.STATUS), 2) AS errors
    FROM log
    GROUP BY DATE (TIME)
    ) a
WHERE a.errors > 1
ORDER BY errors DESC;
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.305471 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO