Divide 2 times by date_trunc

Question

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 10-Apr-18 8:24am

Member 13772596

Updated 10-Apr-18 9:36am

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Answer 1 · 2018-04-10T09:38:00

Solution 1

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!

Posted 10-Apr-18 9:38am

Maciej Los

Updated 10-Apr-18 10:14am

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?