![]() Think of it as a single table with these two values This subquery returns the id of each employee and the SUM of all his calls durations. Between these brackets, we’ve placed the slightly modified query from part #2 SQL Example –ĭATEDIFF Function. Part starting from the 2nd INNER JOIN (INNER JOIN () and ending with ) AS duration_sum ON employee.id = duration_sum.id. The most important is that we’ve placed the entire query returning the aggregated value in the subquery (the Interesting concepts that should be mentioned: This is very useful because youĬould put such calculations inside the SQL query and avoid additional work later. You can notice that we’ve achieved in combining row values with aggregated value. To do that, we’ll use a subquery to calculate that aggregated value and then join into the related row. Hint: We need to combine value calculated for one row with the aggregated value.We also want to know the percentage of time an employee spent on this call, compared to the total call time of all his calls. In our case, you can use combine the SUM function with DATEDIFF without any problem.įor each employee, we need to return all his calls with their duration. Note: There are no special rules when you combine the result returned by any function and aggregate function.Since we’ve grouped everything on the employee level, we have exactly 1 row per employee We’ve used the aggregate function SUM(…) around the previously calculated call duration for each employee ![]() We’ve joined tables call and employee because we need data from both tables (employee details and call duration) Few things I would like to emphasize here are: But let’s comment on how we achieved that. There is nothing special to add regarding the result – we got exactly what we wanted. Sort them first by employee_id and then by start_time. Therefore, we need to select all attributes, and we’ll We want to examine what is in the call table in our model. I’ll use the same approach covered in the article Learn SQL: How to Write a Complex SELECT Query? Let’s start. Each example will add something new, and we’llĭiscuss the learning goal behind each query. ![]() We’ll analyze 6 SQL examples, starting from a pretty simple one. In case, you’re not, just take a quick look at the tables, and how are they related. This is the same model we’re using in this series, so you should be familiar by now. So, buckle up, we’re taking off!Īs always, let’s first take a quick look at the data model we’ll use. We’ll examine queries you could need at the job interview, but also some you would need in real-life situations. The goal of this article is to start with a fairly simple query and move towards more complex queries. In the previous article we’ve practiced SQL,Īnd today, we’ll continue with a few more SQL examples. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |