Select
T1 F_102 as material group,
T2, FName suppliers,
B. the Amount as Amount
The FROM AIS20190922235221. [dbo]. ICStockBill t
Inner join AIS20190922235221. [dbo]. ICStockBillEntry b on T.F InterID=b. InterID
INNER JOIN AIS20190922235221. [dbo]. T_ICItem t1 ON t1. FItemID=b. itemid
INNER JOIN AIS20190922235221. [dbo]. T_Supplier t2 ON t2. FItemID=t.F SupplyID
WHERE T.F TranType='1'

Supplier, the same name, amount to merge how to write a SQL statement?
Asking our great god give directions!!!!!!

CodePudding user response:

The supplier of the same, different material group, how take material set of columns of data,

Below is maximizing the material group,

Select
MAX (t1. F_102) as material group,
T2, FName suppliers,
The SUM (b. Amount) as Amount
The FROM AIS20190922235221. [dbo]. ICStockBill t
Inner join AIS20190922235221. [dbo]. ICStockBillEntry b on T.F InterID=b. InterID
INNER JOIN AIS20190922235221. [dbo]. T_ICItem t1 ON t1. FItemID=b. itemid
INNER JOIN AIS20190922235221. [dbo]. T_Supplier t2 ON t2. FItemID=t.F SupplyID
WHERE T.F TranType='1'
GROUP BY t2. FName

CodePudding user response:

reference 1st floor RINK_1 response:

the suppliers of the same, different material group, how take material set of columns of data,

Below is maximizing the material group,

Select
MAX (t1. F_102) as material group,
T2, FName suppliers,
The SUM (b. Amount) as Amount
The FROM AIS20190922235221. [dbo]. ICStockBill t
Inner join AIS20190922235221. [dbo]. ICStockBillEntry b on T.F InterID=b. InterID
INNER JOIN AIS20190922235221. [dbo]. T_ICItem t1 ON t1. FItemID=b. itemid
INNER JOIN AIS20190922235221. [dbo]. T_Supplier t2 ON t2. FItemID=t.F SupplyID
WHERE T.F TranType='1'
GROUP BY t2. FName

The same supplier merge, the corresponding material group also merge!

CodePudding user response:

refer to the second floor is often little columbium reply:

Quote: refer to 1st floor RINK_1 response:

The supplier of the same, different material group, how take material set of columns of data,

Below is maximizing the material group,

Select
MAX (t1. F_102) as material group,
T2, FName suppliers,
The SUM (b. Amount) as Amount
The FROM AIS20190922235221. [dbo]. ICStockBill t
Inner join AIS20190922235221. [dbo]. ICStockBillEntry b on T.F InterID=b. InterID
INNER JOIN AIS20190922235221. [dbo]. T_ICItem t1 ON t1. FItemID=b. itemid
INNER JOIN AIS20190922235221. [dbo]. T_Supplier t2 ON t2. FItemID=t.F SupplyID
WHERE T.F TranType='1'
GROUP BY t2. FName

The same supplier merge, the corresponding material group also merge!

Mark is to merge into one, then the amount together!

CodePudding user response:

reference 3 floor often small columbium reply:

Quote: refer to the second floor often small columbium reply:

Quote: refer to 1st floor RINK_1 response:

The supplier of the same, different material group, how take material set of columns of data,

Below is maximizing the material group,

Select
MAX (t1. F_102) as material group,
T2, FName suppliers,
The SUM (b. Amount) as Amount
The FROM AIS20190922235221. [dbo]. ICStockBill t
Inner join AIS20190922235221. [dbo]. ICStockBillEntry b on T.F InterID=b. InterID
INNER JOIN AIS20190922235221. [dbo]. T_ICItem t1 ON t1. FItemID=b. itemid
INNER JOIN AIS20190922235221. [dbo]. T_Supplier t2 ON t2. FItemID=t.F SupplyID
WHERE T.F TranType='1'
GROUP BY t2. FName

The same supplier merge, the corresponding material group also merge!

Mark is to merge into one, then the amount together!

Select
T1 F_102 as material group,
T2, FName suppliers,
The SUM (b. Amount) as Amount
The FROM AIS20190922235221. [dbo]. ICStockBill t
Inner join AIS20190922235221. [dbo]. ICStockBillEntry b on T.F InterID=b. InterID
INNER JOIN AIS20190922235221. [dbo]. T_ICItem t1 ON t1. FItemID=b. itemid
INNER JOIN AIS20190922235221. [dbo]. T_Supplier t2 ON t2. FItemID=t.F SupplyID
WHERE T.F TranType='1'
GROUP BY t2 FName, t1. F_102