Click here to Skip to main content
15,038,642 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i have 1 table with 2 columns i work with (time,status)
i select a certain day with
SQL
date_trunc()
in the time column and apply condition where status = '404 NOT FOUND'
and divide that with the daily count
to get a percentage of daily errors
status has 2 values 404 NOT FOUND and 200 OK
i wanna get the daily error percentage

What I have tried:

select case when status = '404 NOT FOUND' then count(time) END / count(time) from log group by date_trunc('day',time);

i get error column "log.status" must appear in the GROUP BY clause or be used in an aggregate function
Posted
Updated 10-Apr-18 9:36am
Comments
Maciej Los 10-Apr-18 14:56pm
   
What database engine?
Nelek 10-Apr-18 15:05pm
   
He has written SQL as TAG for the question, I suppose it is the engine.
Maciej Los 10-Apr-18 15:16pm
   
I mean database engine, which is related to Sql Server, Oracle, PostgreSql, MySql, etc.
As you know, some engines does not provide some functionalities. ;)
Nelek 10-Apr-18 15:32pm
   
as I know? so far had almost nothing to do with databases... I have helped debugging some things but never set one on my own.
Member 13772596 10-Apr-18 15:57pm
   
it's postgresql

1 solution

Seems, you're using PostgreSQL...

The base idea is to get total no. of logs and count of specific errors:
Total:
SQL
SELECT COUNT(*) AS TotalCount
FROM log;


Count of specific error
SQL
SELECT COUNT(*) AS ErrCount
FROM log
WHERE status = '404 NOT FOUND';


Now, you have to "join" both results into single value:
SELECT ErrCount/Total AS Err404
FROM
(
   (<here_comes_second_query>) as ErrCount,
   (<here_comes_first_query>) as Total
   ) as partial_data;



See: sql - How to reuse a result column in an expression for another result column[^]

[EDIT]
SQL
SELECT COUNT(*) / COUNT(CASE WHEN status='404...' THEN 1 END) AS MyResult
FROM log;

Thanks Kornfeld Eliyahu Peter[^] for your valuable suggestion!
   
v2
Comments
Kornfeld Eliyahu Peter 10-Apr-18 15:55pm
   
An other option may be to use of COUNT(*) and COUNT(expression) in the same query and in the right way,,,
Maciej Los 10-Apr-18 16:02pm
   
Do you mean window function or something like this:
SELECT COUNT(*) / COUNT(CASE WHEN status='404...' THEN 1 END) FROM Log;

Yes, you're right!
Thanks for valuable comment.
Member 13772596 10-Apr-18 16:03pm
   
the query above does not work it says error in (,) >>> ErrCount,
Maciej Los 10-Apr-18 16:09pm
   
See Kornfeld Eliyahu Peter's suggestions.
Member 13772596 10-Apr-18 16:17pm
   
exactly the opposite !!! like:
SELECT COUNT(CASE WHEN status='404...' THEN 1 END) / COUNT(*) AS MyResult
FROM log;
and i want them to be grouped by each day
Maciej Los 10-Apr-18 16:22pm
   
Like this:
SELECT date_trunc('day',time) As MyDate, COUNT(CASE WHEN status='404...' THEN 1 END) / COUNT(*) AS MyResult
FROM log
GROUP BY date_trunc('day',time);
Member 13772596 10-Apr-18 16:35pm
   
is it normal for it to return zeros ??? it should return 1 result with 2 i think
Maciej Los 11-Apr-18 2:03am
   
I have no access to your data. How should i know?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900