We help IT Professionals succeed at work.

SQL select statement to summarise the quantity on multiple data rows

Hi experts,

Is it possible to create a SQL select query to summarise the quantity   from a small SQL table for multiple record rows? ie data from the table that contains the same data from the following fields more than once: itemcode, warehouse, bin code but summarised by the total unique quantitys.

Please see attached Excel example of current results, and results id like to be able to see if possible.

currently I have the following sql code:

SELECT T0.[U_BinCode], T0.[U_ItemCode], T0.[U_WhsCode], T0.[U_BatchNum], T0.[U_Qty_Hold], T0.[U_DocDate], T0.[U_AlocQty], SUM (T0.[U_Quantity]) as 'QtySum'
FROM [dbo].[@CTX_BINSTOCK] T0
Group by T0.[U_BinCode], T0.[U_ItemCode], T0.[U_Quantity], T0.[U_WhsCode], T0.[U_BatchNum], T0.[U_Qty_Hold], T0.[U_DocDate], T0.[U_AlocQty]
order by T0.[U_ItemCode]
Sum-Example.xlsx
Comment
Watch Question

CERTIFIED EXPERT

Commented:
What's troubling you is the BatchNum column.
You have to make a decision, you either remove it from your query or use an aggregated function on this field. This could be, select Min or select Max, whatever suits you best.
Jason SchlueterIT Manager

Commented:
I agree with LIONKING.  As long as you're not concerned with which batch number or warehouse each item belongs to, you can try this:
SELECT T0.[U_BinCode],
       T0.[U_ItemCode],
       Min( T0.[U_WhsCode] ) AS 'MinWhsCode',
       Min( T0.[U_BatchNum] ) AS 'MinBatchNum',
       T0.[U_Qty_Hold],
       T0.[U_DocDate],
       T0.[U_AlocQty],
       Sum ( T0.[U_Quantity] ) AS 'QtySum'
FROM   [dbo].[@CTX_BINSTOCK] T0
GROUP  BY T0.[U_BinCode],
          T0.[U_ItemCode],
          T0.[U_Quantity],
          T0.[U_Qty_Hold],
          T0.[U_DocDate],
          T0.[U_AlocQty]
ORDER  BY T0.[U_ItemCode]

Open in new window

But I'm still concerned about the reason for the group by U_Quantity even through it is not in the selection criteria.

Author

Commented:
hmm ok, ideally id like to be able to see the batch number column if this is possible but could probably make do without that field showing if this is not possible. Ive tried removing this field but i still get the same results

Author

Commented:
yes thinking about it more it seems ok to remove the batch number as not so important as other fields, but need the warehouse included.

the U_Quantity is in selection criteria but is set as a summary as i thought this was along the right lines. the sum part could be removed if not needed.
CERTIFIED EXPERT

Commented:
Try this:

SELECT T0.[U_BinCode], T0.[U_ItemCode], T0.[U_WhsCode], T0.[U_Qty_Hold], T0.[U_DocDate], T0.[U_AlocQty], SUM (T0.[U_Quantity]) as 'QtySum'
FROM [dbo].[@CTX_BINSTOCK] T0
Group by T0.[U_BinCode], T0.[U_ItemCode], T0.[U_Quantity], T0.[U_WhsCode], T0.[U_Qty_Hold], T0.[U_DocDate], T0.[U_AlocQty]
order by T0.[U_ItemCode]

Author

Commented:
JasonSchlueter - thanks, i tried your code but still has the bin code multiple times for the same item code even if i remove both warehouse code and batch number lines from the select part

Author

Commented:
LIONKING - just tried your suggestion thanks, but again the results still show the same bin code multiple times for the same item code.

Author

Commented:
Note the quantity column on some of the rows are 0 in case this is causing any issues?

Author

Commented:
As a test I just tried running query with less columns using the below
but results still show the same bin code multiple times for the same item code.

SELECT T0.[U_BinCode], T0.[U_ItemCode], T0.[U_DocDate], SUM (T0.[U_Quantity]) as 'QtySum'
FROM [dbo].[@CTX_BINSTOCK] T0
Group by T0.[U_BinCode], T0.[U_ItemCode], T0.[U_Quantity], T0.[U_DocDate]
order by T0.[U_ItemCode]
CERTIFIED EXPERT

Commented:
If you're summarizing for U_Quantity, you don't wanna put it in your group by clause...
Try

SELECT T0.[U_BinCode], T0.[U_ItemCode], T0.[U_DocDate], SUM (T0.[U_Quantity]) as 'QtySum'
FROM [dbo].[@CTX_BINSTOCK] T0
Group by T0.[U_BinCode], T0.[U_ItemCode], T0.[U_DocDate]
order by T0.[U_ItemCode]

Author

Commented:
LIONKING, ok just tried that and below is an example of the results, as you can see the bincode still shows more than once for the same item for some

U_BinCode      U_ItemCode      U_DocDate          QtySum
02KI0000             1109FBF             2012-07-03       504.000000
REC_BIN             1109FBF             2012-07-19       54432.000000
REC_BIN             1109FBF             2012-07-26       0.000000

Author

Commented:
maybe somehow some of the data needs to go into a temp table and summarised?
CERTIFIED EXPERT

Commented:
It's because you're displaying the DocDate... if you don't want that, then you'll need

SELECT T0.[U_BinCode], T0.[U_ItemCode], SUM (T0.[U_Quantity]) as 'QtySum'
FROM [dbo].[@CTX_BINSTOCK] T0
Group by T0.[U_BinCode], T0.[U_ItemCode]
order by T0.[U_ItemCode]

Author

Commented:
I didn't think so much about that, The docdate is needed unfortunately as one of the key bits of info, is it possible to show the oldest date for each item per bin code?, usually the same date applies when the same item and same bin code and same batch num is used.

Author

Commented:
...the reason for the inclusion of the date field if possible is to show the user the bin codes that have the oldest stock ie the oldest date for each item in each location in each warehouse

Author

Commented:
Or if possible being able to add a where clause to filter results for a specific item code if this helps in any way?
CERTIFIED EXPERT

Commented:
You could add the oldest date.

SELECT T0.[U_BinCode], T0.[U_ItemCode], MIN(T0.[U_DocDate]) AS MinDocDate, SUM (T0.[U_Quantity]) as 'QtySum'
FROM [dbo].[@CTX_BINSTOCK] T0
Group by T0.[U_BinCode], T0.[U_ItemCode]
order by T0.[U_ItemCode]

And if you want you can add the filter as well...
awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
Your example shows the same values for U_Qty_Hold, U_Doc_Date, and
U_AlocQty. Is that really the case or can they be different. If they
are different, what is the criteria to be used to select which value?
Also, the U_BatchNum is different for the first and second row of
U_WhsCode 1 as well as U_WhsCode 2, but they're the same values. Is
this a typo? If not, again what would be the criteria for choosing
one over the other?
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
SELECT
    T0.[U_ItemCode], T0.[U_BinCode],
    MAX(T0.[U_DocDate]) AS [DocDateMax],
    SUM(T0.[U_AlocQty]) AS [QtyAlocSum],
    SUM(T0.[U_Qty_Hold]) AS [QtyHoldSum],
    SUM (T0.[U_Quantity]) AS [QtySum]
FROM [dbo].[@CTX_BINSTOCK] T0
GROUP BY
    T0.[U_ItemCode], T0.[U_BinCode]
ORDER BY
    T0.[U_ItemCode]

Author

Commented:
Thanks  for everyones help, that seems to work fine. I just added the warehouse code & filtered so the quantity is more than 0. So I have the following code:

SELECT T0.[u_itemcode],
       T0.[u_bincode],
       T0.[u_whscode],
       Max(T0.[u_docdate])   AS [DocDateMax],
       Sum (T0.[u_quantity]) AS [QtySum]
FROM   [dbo].[@ctx_binstock] T0
WHERE  T0.[u_quantity] > 0
GROUP BY     T0.[U_ItemCode], T0.[U_BinCode], T0.[U_WhsCode], T0.[U_DocDate]
ORDER BY     T0.[U_ItemCode], T0.[U_DocDate]

Explore More ContentExplore courses, solutions, and other research materials related to this topic.