AdventureWorks Database: Expression used in group by

SQL Query - AdventureWorks: Exercise-10 with Solution

10. From the following table write a query in SQL to find the total quentity for a group of locationid multiplied by 10.

productid|locationid|shelf|bin|quantity|rowguid                             |modifieddate           |
---------+----------+-----+---+--------+------------------------------------+-----------------------+
        1|         1|A    |  1|     408|47a24246-6c43-48eb-968f-025738a8a410|2014-08-08 00:00:00.000|
        1|         6|B    |  5|     324|d4544d7d-caf5-46b3-ab22-5718dcc26b5e|2014-08-08 00:00:00.000|
        1|        50|A    |  5|     353|bff7dc60-96a8-43ca-81a7-d6d2ed3000a8|2014-08-08 00:00:00.000|
        2|         1|A    |  2|     427|f407c07a-ca14-4684-a02c-608bd00c2233|2014-08-08 00:00:00.000|
        2|         6|B    |  1|     318|ca1ff2f4-48fb-4960-8d92-3940b633e4c1|2014-08-08 00:00:00.000|
        2|        50|A    |  6|     364|d38cfbee-6347-47b1-b033-0e278cca03e2|2014-08-08 00:00:00.000|
        3|         1|A    |  7|     585|e18a519b-fb5e-4051-874c-58cd58436c95|2008-03-31 00:00:00.000|
        3|         6|B    |  9|     443|3c860c96-15ff-4df4-91d7-b237ff64480f|2008-03-31 00:00:00.000|
        3|        50|A    | 10|     324|1339e5e3-1f8e-4b82-a447-a8666a264f0c|2008-03-31 00:00:00.000|
        4|         1|A    |  6|     512|6beaf0a0-971a-4ce1-96fe-692807d5dc00|2014-08-08 00:00:00.000|
        4|         6|B    | 10|     422|2c82427a-63f1-4877-a1f6-a27b4d201eb6|2014-08-08 00:00:00.000|
        4|        50|A    | 11|     388|fd912e69-efa2-4ab7-82a4-03f5101af11c|2014-08-08 00:00:00.000|
      316|         5|A    | 11|     532|1ee3dbd3-2a7e-47dc-af99-1b585575efb9|2008-03-31 00:00:00.000|
	  -- more --

Click to view Full table

Sample Solution:

-- Selecting the sum of quantities grouped by a derived column calculated as (locationid * 10)
SELECT SUM(quantity) AS total_quantity
-- Retrieving data from the 'productinventory' table in the 'production' schema
FROM production.productinventory
-- Grouping the results by a derived column calculated as (locationid * 10)
GROUP BY (locationid * 10);

Explanation:

• The SELECT SUM(quantity) AS total_quantity statement calculates the sum of quantities for each group.
• FROM production.productinventory specifies the table from which the data will be retrieved. 'production' is the schema name and 'productinventory' is the table name.
• The GROUP BY (locationid * 10) clause groups the results by a derived column calculated as (locationid * 10). This derived column is used to create groups based on a modified version of the 'locationid'.
• Each group represents a range of location IDs, where each range spans 10 location IDs.
• The SUM(quantity) function calculates the total quantity of products within each group.
• The result provides the total quantity of products for each group of location IDs, where each group represents a range of 10 location IDs.

Sample Output:

total_quantity|
--------------+
           186|
         20295|
           110|
         83173|
         17319|
          5549|
         72899|
           958|
         13584|
           332|
         95477|
          5165|
         20419|
           508|

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Find total quantity of each product in specific shelves.
Next: Persons whose last name begins with 'L'.