Error: Subquery returned more than 1 value. This is not permitted when the subquery follows

user_18494695 0
Viewed 162

I got this kind of table Table Actividades_Usuarios I already tried to obtain the following query and it works fine

SELECT DISTINCT 
    USER_ID, SESSION_ID,
    (SELECT EVENT_TIME FROM Actividades_Usuarios 
     WHERE EVENT_ID = 1 AND USER_ID = 407) AS START_TIME_SESSION,
    (SELECT EVENT_TIME FROM Actividades_Usuarios 
     WHERE EVENT_ID = 2 AND USER_ID = 407) AS END_TIME_SESSION,
    DATEDIFF(mi, (SELECT EVENT_TIME FROM Actividades_Usuarios 
                  WHERE EVENT_ID = 1 AND USER_ID = 407),
                 (SELECT EVENT_TIME FROM Actividades_Usuarios 
                  WHERE EVENT_ID = 2 AND USER_ID = 407)) AS TIME_SESSION 
FROM 
    Actividades_Usuarios 
WHERE 
    USER_ID = 407

but for a single user, for example:

Then I try to make it out for the general case, setting EVENT*_*TIME as START_TIME_SESSION when EVENT_ID is 1, instead END_TIME_SESSION when EVENT_ID is 2, with the follow query:

SELECT DISTINCT USER_ID,
       SESSION_ID,
       (SELECT EVENT_TIME FROM Actividades_Usuarios 
WHERE EVENT_ID = 1) as START_TIME_SESSION,
       (SELECT EVENT_TIME FROM Actividades_Usuarios 
WHERE EVENT_ID = 2) as END_TIME_SESSION
FROM Actividades_Usuarios

but get the common error:

Subquery returned more than 1 value...

when I'm looking exactly that query result. In fact, if I go run one of the column as a simple query, returns the same quantity of rows, whatever select...

SELECT USER_ID,SESSION_ID,
EVENT_TIME as START_TIME_SESSION 
FROM Actividades_Usuarios 
WHERE EVENT_ID = 1

But, how can I do this adding the other column (END_TIME_SESSION) and the final goal column result (TIME_SESSION) from DATEDIFF between these two?

subquery datediff distinct select sql
2 Answers

You need to correlate all of your subqueries i.e. add to you WHERE clauses in your subqueries something along the lines of “user id of subquery = user id of main query”

Clarifying my answer with an example…

SELECT DISTINCT 
A.USER_ID,
A.SESSION_ID,
(SELECT B.EVENT_TIME 
 FROM Actividades_Usuarios B
 WHERE B.EVENT_ID = 1 
 AND B.USER_ID = A.USER_ID) as START_TIME_SESSION,
(SELECT C.EVENT_TIME 
 FROM Actividades_Usuarios C
 WHERE C.EVENT_ID = 2 
 AND C.USER_ID = A.USER_ID) as END_TIME_SESSION
FROM Actividades_Usuarios A
user_136583990

What you really want to use is aggregation and avoid all the subqueries in the first place:

select USER_ID, SESSION_ID,
    min(case when EVENT_ID = 1 then EVENT_TIME end) as START_TIME_SESSION,
    min(case when EVENT_ID = 2 then EVENT_TIME end) as END_TIME_SESSION
from Actividades_Usuarios
where EVENT_ID in (1, 2)
group by USER_ID, SESSION_ID;

Using CTEs will make calculations a breeze:

with data as (
    select USER_ID, SESSION_ID,
        min(case when EVENT_ID = 1 then EVENT_TIME end) as START_TIME_SESSION,
        min(case when EVENT_ID = 2 then EVENT_TIME end) as END_TIME_SESSION
    from Actividades_Usuarios
    where EVENT_ID in (1, 2)
    group by USER_ID, SESSION_ID
)
select *, datediff(minute, START_TIME_SESSION, END_TIME_SESSION) as TIME_SESSION
from data;

You should also look into the way that datediff only counts interval boundaries. I suspect the result you want is more like this:

datediff(millisecond, START_TIME_SESSION, END_TIME_SESSION) / 60000 as TIME_SESSION
user_13222680
Related