Group query example

The columns in the select list queried by group can only be columns in group by, otherwise they can only be placed in aggregate functions. The queried information is incomplete. The following query is used to query more columns.

 

Input is the item warehouse receiving table, total is the quantity, unitprice is the unit price, product_id is the foreign key reference from the input_categories table

Create   Table   [ DBO ] . [ Input ] (
[ ID ]   [ Int ]   Identity ( 1 , 1 ) Not   Null ,
[ Product_id ]   [ Int ]   Not   Null ,
[ Unitprice ]   [ Float ]   Null ,
[ Total ]   [ Int ]   Null ,

 

Input_categories (productname is the product name ):

Create   Table   [ DBO ] . [ Input_categories ] (
[ ID ]   [ Int ]   Identity ( 1 , 1 ) Not   Null ,
[ Productname ]   [ Nvarchar ] ( 50 ) Not   Null )

 

 

What you want to query is the unit price of the last warehouse receiving for each item and the sum of the items.

Let's take a look at the data in two tables first:

You can use the following query:

Code

Select   *   From
(
Select   * , Ran = Row_number () Over (Partition By Productname Order   By ID Desc )
From
( Select C. ID, B. productname, B. Total, C. unitprice From
( Select Productname, Sum (Total) As Total From
( Select I. ID, IC. productname, I. unitprice, I. Total
From Input As I, input_categories As IC Where I. product_id = IC. ID) T
Group   By Productname) B ,( Select I. ID, IC. productname, I. unitprice, I. Total
From Input As I, input_categories As IC Where I. product_id = IC. ID) c
Where B. productname = C. productname) h
) G
Where G. Ran <= 1

 

The following is a breakdown of the query:

1. Because the two tables have a primary-foreign key relationship, the two tables are combined into one through joint query.

SelectI. ID, IC. productname, I. unitprice, I. Total
FromInputAsI, input_categoriesAsICWhereI. product_id=IC. ID

2. Perform grouping statistics.

Select Productname, Sum (Total) As Total From
( Select I. ID, IC. productname, I. unitprice, I. Total
From Input As I, input_categories As IC Where I. product_id = IC. ID) T
Group   By Productname

3. Only productname and total are left after the group. In order to include more information, you can perform a connection query (connection query in steps 2 and 1)

Select C. ID, B. productname, B. Total, C. unitprice From
( Select Productname, Sum (Total) As Total From
( Select I. ID, IC. productname, I. unitprice, I. Total
From Input As I, input_categories As IC Where I. product_id = IC. ID) T
Group   By Productname) B ,( Select I. ID, IC. productname, I. unitprice, I. Total
From Input As I, input_categories As IC Where I. product_id = IC. ID) c
Where B. productname = C. productname

4. Insert a sequence using row_number.

Select   * , Ran = Row_number () Over (Partition By Productname Order   By ID Desc )
From
( Select C. ID, B. productname, B. Total, C. unitprice From
( Select Productname, Sum (Total) As Total From
( Select I. ID, IC. productname, I. unitprice, I. Total
From Input As I, input_categories As IC Where I. product_id = IC. ID) T
Group   By Productname) B ,( Select I. ID, IC. productname, I. unitprice, I. Total
From Input As I, input_categories As IC Where I. product_id = IC. ID) c
Where B. productname = C. productname) h
) G

5. Finally, set the unit price for the last warehouse receiving. The ran with the largest ID is exactly 1, so it is OK to filter the records with ran = 1.

This is a big success, oh yeah !!