AdventureWorks Database: Expression used in group by

Last update on December 20 2024 11:37:12 (UTC/GMT +8 hours)

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

Sample table: production.productinventory

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'.