?
Solved

SQL select statement to summarise the quantity on multiple data rows

Posted on 2012-08-16
20
Medium Priority
?
619 Views
Last Modified: 2012-08-17
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
0
Comment
Question by:kevin1983
20 Comments
 
LVL 13

Expert Comment

by:LIONKING
ID: 38300957
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.
0
 
LVL 5

Expert Comment

by:Jason Schlueter
ID: 38301078
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.
0
 

Author Comment

by:kevin1983
ID: 38301079
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
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:kevin1983
ID: 38301099
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.
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38301107
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]
0
 

Author Comment

by:kevin1983
ID: 38301116
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
0
 

Author Comment

by:kevin1983
ID: 38301142
LIONKING - just tried your suggestion thanks, but again the results still show the same bin code multiple times for the same item code.
0
 

Author Comment

by:kevin1983
ID: 38301152
Note the quantity column on some of the rows are 0 in case this is causing any issues?
0
 

Author Comment

by:kevin1983
ID: 38301171
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]
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38301233
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]
0
 

Author Comment

by:kevin1983
ID: 38301303
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
0
 

Author Comment

by:kevin1983
ID: 38301364
maybe somehow some of the data needs to go into a temp table and summarised?
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38301379
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]
0
 

Author Comment

by:kevin1983
ID: 38301629
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.
0
 

Author Comment

by:kevin1983
ID: 38301650
...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
0
 

Author Comment

by:kevin1983
ID: 38301680
Or if possible being able to add a where clause to filter results for a specific item code if this helps in any way?
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38301729
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...
0
 
LVL 32

Expert Comment

by:awking00
ID: 38301836
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?
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 38302564
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]
0
 

Author Closing Comment

by:kevin1983
ID: 38305563
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]
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question