/* This query lists the percentage of total units in stock for each product. The subquery select sum(UnitsInStock) from products works out the total units in stock which is 3119. Then 3119 is used in the outer query to calculate the percentage of total units in stock for each product. */ select ProductID, ProductName, concat ( ( UnitsInStock / ( select sum ( UnitsInStock ) from products ) ) * 100, '% ' ) as Percent_of_total_units_in_stock from products order by ProductID; /* This query returns the same result as query above. Here 3119 is hardcoded whereas query above uses subquery to calculate 3119 on the fly. */ select ProductID, ProductName, concat ( ( UnitsInStock / 3119 ) * 100, '% ' ) as Percent_of_total_units_in_stock from products order by ProductID; Query result set - 77 rows returned: Other tutorials in this category 1. Copyright © 2018 GeeksEngine.com. Oct 28, 2008 - Subquery-related keywords such as IN, ANY, SOME, and EXISTS only return TRUE or FALSE, making them ideal for use in WHERE clause subqueries. You can also use correlated subqueries in the WHERE clause of UPDATE and DELETE statements to narrow down which rows are affected by the statement. I am using below update statement and it is way too slow(it takes around 10-12 seconds). I cannot convert correlated subquery to join as it is using distinct clause.Plan of this query gives below result. MySQL update with subselect too slow. All Rights Reserved. This website is hosted. How to install a self signed certificate in iis 6 download. No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. And I will promptly and gladly rectify it.|||.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |