CODEWITHSUNDEEP
mysqlsqldatabase
JohnnyRingo506
JohnnyRingo506

Reputation: 25

Operand should contain 1 column(s) error on my sql query

Not sure where this is coming from. My intent is to have ROUND(AVG(duration))be divided with the difference of lead_at and qualified_at (the second subquery)

SELECT (SELECT first_name 
        FROM users_permissions p WHERE p.user_id=a.assistant_id LIMIT 1) as Name, (SELECT u.lead_at, u.qualified_at FROM users u INNER JOIN aircalls ON u.id=aircalls.user_id WHERE u.qualified_at - u.lead_at) AS Difference, ROUND(AVG(duration)), count(id)
FROM aircalls a
WHERE assistant_id IS NOT NULL 
AND status='Call Connected' 
AND type='user' 
AND EXISTS (SELECT u.id FROM users u INNER JOIN aircalls ON u.id=aircalls.user_id AND u.status NOT IN (1,7))
AND EXISTS (SELECT p.user_id FROM users_permissions p INNER JOIN aircalls ON p.user_id=aircalls.user_id AND p.is_agent NOT IN (1))
AND year(a.created_at)=2019
GROUP BY(SELECT first_name 
         FROM users_permissions p WHERE p.user_id=a.assistant_id LIMIT 1)

Upvotes: 0

Views: 78

Answers (1)

Caldazar
Caldazar

Reputation: 3757

Issue is in your second select (difference), instead of deducing two values, you've just put them both separated by comma. Also, where clause in that select doesn't have valid value.

It was: 

SELECT u.lead_at, u.qualified_at FROM users u INNER JOIN aircalls ON u.id=aircalls.user_id WHERE u.qualified_at - u.lead_at) AS Difference

Here's correction for that wrong line:

(SELECT u.lead_at - u.qualified_at FROM users u INNER JOIN aircalls ON u.id=aircalls.user_id) AS Difference

Upvotes: 1

Related Questions