Sub selects within MySQL query

Question

I am wanting to alias 3 variables in my query, total_time_taken and average and request_count.

The average is meant to calculate the total_time_taken / request_count to return average however it is giving me a syntax error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select(created_at, assigned_at, SUM(TIMESTAMPDIFF(SECOND, requests.created_at, r' at line 4

The query is below.

select
    *,
    COUNT(*) as request_count,
    select(created_at, assigned_at, SUM(TIMESTAMPDIFF(SECOND, requests.created_at, requests.assigned_at)) from requests) as total_time_taken,
    total_time_taken / request_count as average
from
    `requests`
where
    `deleted_at` is null
and
    `submitted_at` >= '2017-03-30 00:00:00'
and
    `requests`.`deleted_at` is null
group by
    `engineer_id`
limit 5

You are missing an ( in front of the 2nd select

Shadow
– Shadow

2017-04-03 09:10:35 +00:00
Commented Apr 3, 2017 at 9:10 — Shadow
– Shadow, Commented Apr 3, 2017 at 9:10
the sub select needs to be wrapped in ()? @Shadow

Ian
– Ian

2017-04-03 09:12:12 +00:00
Commented Apr 3, 2017 at 9:12 — Ian
– Ian, Commented Apr 3, 2017 at 9:12

Tim Biegeleisen · Accepted Answer · 2017-04-03 09:22:27Z

1

I don't see the point of the subqueries, why not just take the sum as you aggregate by each engineer? Alse, you were doing SELECT * with GROUP BY, which usually doesn't work, and usually is wrong, because it would include non aggregate columns. Instead, just select engineer_id or an aggregate of some other column.

SELECT
    engineer_id,
    COUNT(*) AS request_count,
    SUM(TIMESTAMPDIFF(SECOND, created_at, assigned_at)) AS total_time_taken,
    SUM(TIMESTAMPDIFF(SECOND, created_at, assigned_at)) /
    COUNT(*) AS average
FROM requests
WHERE deleted_at IS NULL AND
      submitted_at >= '2017-03-30 00:00:00' AND
      deleted_at IS NULL
GROUP BY engineer_id
LIMIT 5

edited Apr 3, 2017 at 9:22

answered Apr 3, 2017 at 9:15

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

3 Comments

Ian Over a year ago

Getting the error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT SUM(TIMESTAMPDIFF(SECOND, requests.created_at, requests.assigned_at)) / ' at line 5

However you have nailed exactly what I want to do.

Tim Biegeleisen Over a year ago

You probably want to use ORDER BY with this query, since you are using LIMIT. As it stands now, you would be returning 5 arbitrary engineers, probably not what you want.

Tim Biegeleisen Over a year ago

As I mentioned above, add ORDER BY <something> to the end of the query to get five engineers according to some order, assuming you want that.

Collectives™ on Stack Overflow

Sub selects within MySQL query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related