List the Products Ordered in a Period | Leetcode 1327 | Crack SQL Interviews in 50 Qs

[@learnwithchirag]

Hey there! 👋 For more interesting content, tutorials, and updates, Feel free to connect with me on Instagram Handles :- @createwithchirag - instagram.com/createwithchirag/ @learn.with.chirag - instagram.com/learn.with.chirag/ LinkedIn: www.linkedin.com/in/chirag-sehgal-9200111b8/ Let's stay connected and keep the creativity flowing! 💡

[@govindjha4457]

table joining mei kaise pta clta hai kaun sa table phle lena hai

[@NitinKumar-wm2dg]

Sir, duplicates handle karne ke liye kya hua, usko alag se handle nahi karenge? aur mera code tha, usme ek testcase pass nahi hora, please help. - with UnitSold as ( select product_id as product_id,sum(unit) as unit from ( select distinct * from Orders where month(order_date)=2 and year(order_date)=2020 ) t group by product_id) select product_name,unit from products p inner join UnitSold u where p.product_id=u.product_id having unit>=100;

[@AjBro15]

I think in the having , it should be having sum(unit) >=100 because it filters out the groups (products) where the total sum of units is less than 100.

[@learnwithchirag]

Here 'UNIT' in the having clause is referring to the column created in the select statement 'UNIT'. In other words, they both are the same column. Having clause is filtering out all values less than 100. You can either use an alias or the condition itself. Happy Learning💯

[@AjBro15]

Ok, i got it thank you. Just one doubt, according to order of execution the having will go first then the select will run, how is having {unit} is getting refer to the alias UNIT?

[@learnwithchirag]

@@AjBro15 The Order of Execution is- FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY This is a conceptual order, and MySQL processes the query in a way that respects these logical steps. However, MySQL allows the use of column aliases in the ORDER BY and HAVING clauses, even though those clauses are logically processed after the SELECT clause. You can treat this an exception in "MYSQL".

[@AjBro15]

@@learnwithchirag got it, thank you Chirag for the explanation..🙌..

[@sayanchakraborty2194]

can u explain this line " having unit >=100 " and if I write " having count(unit) >=100 " what will be happen pls explain.

[@learnwithchirag]

"HAVING unit >= 100": This line is a part of the HAVING clause, which is used to filter the groups produced by the GROUP BY clause. In this case, unit >= 100 specifies a condition that filters the groups based on the aggregated value of the unit column. It ensures that only those groups where the sum of unit is greater than or equal to 100 are included in the result. So, this line ensures that only products with at least 100 units ordered in February 2020 are selected. "HAVING count(unit) >= 100": If you were to use count(unit) instead, it would count the number of non-null values in the unit column within each group. This would mean you're checking for groups where there are at least 100 non-null unit values. However, this is not the same as checking if the sum of unit is greater than or equal to 100. So, using count(unit) in this context would not achieve the desired result of selecting products with at least 100 units ordered. In summary, using HAVING unit >= 100 correctly filters the groups based on the sum of unit values, ensuring that only products with at least 100 units ordered are selected for the result. Using HAVING count(unit) >= 100 would not provide the correct filtering criteria in this context.

[@VikashKumar-sn8zj]

Nahi aaya samaj mai kya kare jo samaj aajaye

[@learnwithchirag]

Hi Vikash , if you are new to this channel then I will suggest you to watch the complete Playlist from the start, Ques 1 to Ques 50. It will build up your concepts and u will be able to understand everything! And if you are following the playlist and not able to understand this particular question , please let me know which part or which line of the query is not clear to you ! I will try my best to explain to you....