ProgrammingError: column "date" doesn't exist

Question

I'm new to SQL and currently try to resolve a data table problem.

I have a data table and now need to find firstly the dates, on which a request lead to an error. They are pulled as timestamps from the log database. Afterwards the status is checked where not status = '200 OK' and the days on which more than 1% of requests lead to an error are shown having count(*) > 0.01,order by num desc.

def number_one_error():
    """
        Percentage of errors from requests
        Counting errors and timestamps
        Output:
            number one errors
    """
    db = psycopg2.connect(database=dbname)
    c = db.cursor()
    c.execute('''
select date
from (select date(log.time) AS date_column,
        count (*) as request_error
        from log where not status = '200 OK'
    group by log.time) as oneerror
join (select date(log.time) AS date_column,
        count(*) as requests
    from log
    group by log.time) as total
on oneerror.date_column = total.date_column
where (round(((oneerror.request_error)/all_requests),3> 0.01))
              ''')
    number_one_error = c.fetchall()
    db.close()


psycopg2.ProgrammingError: column "date" does not exist
LINE 2: select date

None of the sub-queries return a column "date", they return "date_column". — jarlh
– jarlh, Commented Feb 3, 2019 at 20:05

Joakim Danielson · Accepted Answer · 2019-02-10 17:19:35Z

1

You are referring to the wrong column as pointed out and if you want your column to be named as date you can use an alias also in your outer query

SELECT oneerror.date_column AS date

The below suggestion is irrelevant since the question was tagged with mysql when in fact the database is PostgreSql (the use of psycopg2was the clue)

When I look a little closer at your query I notice some errors in it, you're doing GROUP BY on a timestamp type when it is clear you want your data per date and the join is not necessary at all. This is my version which I think will work better. I count both error requests and all requests in the same query where the COUNT(CASE...) will count only error requests. Notice that I am using my alias in both the GROUP BY and HAVING

SELECT date_column as date, 100 * ROUND(error/ok, 3) as percent
FROM (SELECT DATE(time) as date_column, 
      COUNT(*) as ok, 
      COUNT(CASE WHEN status != '200 OK' THEN 1 ELSE NULL END) as error
      FROM log
      GROUP BY date_column
) s
HAVING (percent > 1)

edited Feb 10, 2019 at 17:19

answered Feb 3, 2019 at 20:48

Joakim Danielson

53.1k5 gold badges36 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Eva S Over a year ago

Hi @Joakim Danielson, thanks a lot for your help. Unfortunately I still have this error message: Following the dates for >1 percent requests, leading to an error: Traceback (most recent call last): File "LOG_final_4-test.py", line 127, in <module> print overview() File "LOG_final_4-test.py", line 123, in overview print number_one_error() File "LOG_final_4-test.py", line 92, in number_one_error ''') psycopg2.ProgrammingError: column "percent" does not exist LINE 9: HAVING (percent > 1) Thank you so much for your help!

Joakim Danielson Over a year ago

@Eva I just googled "psycopg2" and it is an adapter for Postgresql so you have tagged your question with the wrong database. My suggestion was for MySql, so forget about it and use your original query instead

Gordon Linoff · Accepted Answer · 2019-02-03 20:20:11Z

0

I think the error is pretty clear. You have no column called date.

I suspect you want:

select oneerror.date_column

answered Feb 3, 2019 at 20:20

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

ProgrammingError: column "date" doesn't exist

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related