1

Topic: To count the goods

Hello;
There is a request which deduces all the line long orders:

SELECT Material. Number
,SalesOrderItem. Quantity
,Material. ImportCode
,Material. NetWeight
,Material. GrossWeight
FROM [DWH_Dev2]. [dbo]. [SalesOrderItem]
INNER JOIN [dbo].Material ON [dbo].Material. MaterialID = [dbo].SalesOrderItem. MaterialID

How to deduce the goods + total for each article?

2

Re: To count the goods

user8474542;
count (goods) over (partition by the article)

3

Re: To count the goods

user8474542;

SELECT Material. Number
,SUM (SalesOrderItem. Quantity) AS [Total]
FROM [DWH_Dev2]. [dbo]. [SalesOrderItem]
INNER JOIN [dbo].Material ON [dbo].Material. MaterialID = [dbo].SalesOrderItem. MaterialID
GROUP BY Material. Number

4

Re: To count the goods

Though, probably

  - Eh wrote:

user8474542;
SUM (goods) over (partition by the article)

or still something.... More shortly, the task specify, and that setting painfully the fuzzy.

5

Re: To count the goods

Kind  - Eh,
Most likely yes, since in SalesOrderItem. Quantity the amount is already probably stored.
Too the first thought was about window function

6

Re: To count the goods

Lines with the article are not unique, as we address to the table of lines of orders. Therefore a variant offered vladislav109 not absolutely true.
The task to deduce unique articles and to count total, i.e. to add SalesOrderItem. Quantity for everyone unique Material. Number

7

Re: To count the goods

user8474542;

count (SalesOrderItem. Quantity) over (PARTITION by material.number)

Like that that is necessary, but one problem, the same lines by an amount of the counted are deduced.
For example, 1 counted 30 as the result is deduced 30 lines. In what we can be a problem?

8

Re: To count the goods

user8474542;
COUNT to replace on SUM
Above wrote

9

Re: To count the goods

To tell the truth the same bed-sheet, a heap of doubling lines...

10

Re: To count the goods

user8474542;
Use GROUP BY. As the aggregate - COUNT also there will be to you a happiness.

11

Re: To count the goods

COUNT here why that incorrectly works, SUM - is correct.
The error was in that that forgot to specify DISTINCT.
Truly so:

SELECT
DISTINCT Material. Number
,sum (SalesOrderItem. Quantity) OVER (PARTITION by material.number order by material.number)