TheDeadMan
November 20, 2019
240 views
0 votes
2 Answers

I have two table events and session which looks like this

Events table

Sessions table

I want to display data based on date ranges

Here is my solution

SELECT sessions.sid, events.datetime, count(*) as num_rows, 
  count(distinct sessions.sid) as sessions, 
  sum( targetbuttonname = 'kredyt' ) as num_kredyt, 
  sum( targetbuttonname = 'konto' ) as num_konto,
  sum( targetbuttonname = 'czat' ) as num_czat,
  sum( targetbuttonname = 'video-voice_btns' ) as num_voice,
  sum( targetbuttonname = 'video-close_btn' )  as num_close,
  sum( targetbuttonname = 'video-muted_btn' ) as num_muted,
  sum( targetbuttonname = 'video-play_btn' )  as num_play,
  sum( targetbuttonname = 'video-pause_btn' )  as num_pause,
  sum( targetbuttonname = 'video-replay_btn' ) as num_replay, 
  sum(watchtime) as num_watchtime, 
  sum(devicetype ='Computer') as num_computer from events 
  INNER JOIN sessions ON (events.sid =sessions.sid) 
  WHERE datetime BETWEEN '2019-11-11' AND '2019-11-21'

Unfortunately when i run the script on phpmyadmin I am getting the following error

1052 – Column: ‘datetime’ in where clause is ambiguous

What am I doing wrong in my code?

Here is jsfiddle demo

Answers

- AntonioAlvarez
- November 20, 2019 at 1:14 pm
- 0 votes
0
Use either WHERE events.datetime BETWEEN '2019-11-11' AND '2019-11-21' or WHERE sessions.datetime BETWEEN '2019-11-11' AND '2019-11-21' depending on the datetime you want to take into account.

A column with name datetime exists in both tables, so you have to qualify the column name.

Login or Signup to reply.

- ErgiNushi
- November 20, 2019 at 1:14 pm
- 0 votes
0
You have “datetime” column in both of your tables. So when you are saying:
```
INNER JOIN sessions ON (events.sid = sessions.sid) 
WHERE datetime BETWEEN '2019-11-11' AND '2019-11-21'
```
it doesn’t know which table your “datetime” is referring.

What you need to do is to refer the column from which table you are using like:
```
INNER JOIN sessions ON (events.sid = sessions.sid) 
WHERE sessions.datetime BETWEEN '2019-11-11' AND '2019-11-21'
```
Or like:
```
INNER JOIN sessions ON (events.sid = sessions.sid) 
WHERE events.datetime BETWEEN '2019-11-11' AND '2019-11-21'
```
Login or Signup to reply.