[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Howdo sum entries for this JOINED column

Posted on 2009-04-27
15
Medium Priority
?
329 Views
Last Modified: 2012-06-27
I have this query which return the correct numbers, except for multiple ITEMS in ICILOC where USED = 1 and LOCATION (not included yet) may have multiple entries.

I need to add the locations together for ISNULL([ICILOC].[QTYONHAND], 0) AS TTlQtyInventory as the qtyonhand should be summed for all locations..


SELECT DISTINCT [ICITEM].[ITEMNO], ISNULL(SUM([OESHHD].[QTYSOLD]), 0) AS TTlQtySold, ISNULL(SUM([PORCPL].[SQRECEIVED]), 0) AS TTlQtyRecieved, ISNULL([ICILOC].[QTYONHAND], 0) AS TTlQtyInventory, ISNULL(sum(CAST([ICILOC].[LOCATION] AS int)), 0) as TTlLocations
FROM ICITEM  LEFT JOIN OESHHD ON ITEM = ITEMNO AND [YR] = '2009' and [PERIOD] = '7'
LEFT JOIN PORCPL ON [PORCPL].[ITEMNO] = [ICITEM].[ITEMNO] AND [DTARRIVAL] < '20090131' and [DTARRIVAL] > '20090101'
LEFT JOIN ICILOC ON [ICILOC].[ITEMNO] = [ICITEM].[ITEMNO] AND [USED] = 1
GROUP BY [ICITEM].[ITEMNO], ITEM,[ICILOC].[QTYONHAND]
ORDER BY [ICITEM].[ITEMNO]

The part "ISNULL(sum(CAST([ICILOC].[LOCATION] AS int)), 0) as TTlLocations" is erroneous and was just inserted for this example.

For example:
the current query returns this:
DXLCD24BC                     67.0000      .0000      1.0000           195
DXLCD24BC                     67.0000      .0000      1372.0000      13

And I need
DXLCD24BC                     67.0000      .0000      1373.0000           ? Dont Care

Your help is greatly appreciated.
0
Comment
Question by:debuggerau
  • 11
  • 3
15 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24247683
Having both Distinct and Group by spoils the Group by Operation.
Try this one out:
SELECT [ICITEM].[ITEMNO], ISNULL(SUM([OESHHD].[QTYSOLD]), 0) AS TTlQtySold, 
ISNULL(SUM([PORCPL].[SQRECEIVED]), 0) AS TTlQtyRecieved, ISNULL([ICILOC].[QTYONHAND], 0) AS TTlQtyInventory, 
ISNULL(sum(CAST([ICILOC].[LOCATION] AS int)), 0) as TTlLocations
FROM ICITEM  LEFT JOIN OESHHD ON ITEM = ITEMNO AND [YR] = '2009' and [PERIOD] = '7'
LEFT JOIN PORCPL ON [PORCPL].[ITEMNO] = [ICITEM].[ITEMNO] AND [DTARRIVAL] < '20090131' and [DTARRIVAL] > '20090101'
LEFT JOIN ICILOC ON [ICILOC].[ITEMNO] = [ICITEM].[ITEMNO] AND [USED] = 1
GROUP BY [ICITEM].[ITEMNO], ITEM,[ICILOC].[QTYONHAND]
ORDER BY [ICITEM].[ITEMNO]

Open in new window

0
 
LVL 23

Author Comment

by:debuggerau
ID: 24247755
yea, I tried removing the DISTINCT, but that made no difference, still get both entries listed separately, rather than being added..

Any other suggestions?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24247802
One doubt,

the current query returns this:
DXLCD24BC                     67.0000      .0000      1.0000           195
DXLCD24BC                     67.0000      .0000      1372.0000      13

And I need
DXLCD24BC                     67.0000      .0000      1373.0000  

We are using two columns in Group by statement. If you need to sum up the fourth column alone, then what about other columns?
Either include them in GROUP BY Clause or Remove SUM criteria in SELECT clause.
Give this a try:
SELECT [ICITEM].[ITEMNO], SUM(ISNULL([OESHHD].[QTYSOLD], 0)) AS TTlQtySold, 
SUM(ISNULL([PORCPL].[SQRECEIVED], 0)) AS TTlQtyRecieved, ISNULL([ICILOC].[QTYONHAND], 0) AS TTlQtyInventory, 
sum(ISNULL(CAST([ICILOC].[LOCATION] AS int), 0)) as TTlLocations
FROM ICITEM  LEFT JOIN OESHHD ON ITEM = ITEMNO AND [YR] = '2009' and [PERIOD] = '7'
LEFT JOIN PORCPL ON [PORCPL].[ITEMNO] = [ICITEM].[ITEMNO] AND [DTARRIVAL] < '20090131' and [DTARRIVAL] > '20090101'
LEFT JOIN ICILOC ON [ICILOC].[ITEMNO] = [ICITEM].[ITEMNO] AND [USED] = 1
GROUP BY [ICITEM].[ITEMNO], ISNULL([ICILOC].[QTYONHAND], 0)
ORDER BY [ICITEM].[ITEMNO]

Open in new window

0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 23

Author Comment

by:debuggerau
ID: 24247862
Thanks for that suggestion, however, same results...

I'm now trying this simpler query, also shows the same symptoms.

SELECT DISTINCT [ICILOC].[ITEMNO], sum([ICILOC].[QTYONHAND])
FROM ICILOC
GROUP BY [ICILOC].[QTYONHAND],[ICILOC].[ITEMNO]
ORDER BY [ICILOC].[ITEMNO]


100CAB                        1            35.0000
100CAB                        12          .0000
100CAB                        15          .0000
100CAB                        2            .0000
100CAB                        6            .0000
100EXPBCAB           1             9.0000
100EXPCAB             1             33.0000
100EXPCAB             2             .0000

so I want to disregard the second column, and sum the third...

I'm not sure it can even be done...
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24247899
In the query shown above, it has only two columns selected and in the result set there are 3 columns. Kindly confirm.
0
 
LVL 23

Author Comment

by:debuggerau
ID: 24247913
sry about that:
my cut and pastes got out of sync...

SELECT DISTINCT [ICILOC].[ITEMNO],[ICILOC].[LOCATION], sum([ICILOC].[QTYONHAND]) as raster
FROM ICILOC
GROUP BY [ICILOC].[QTYONHAND],[ICILOC].[ITEMNO],[ICILOC].[LOCATION]
ORDER BY [ICILOC].[ITEMNO]
0
 
LVL 23

Author Comment

by:debuggerau
ID: 24247963
thanks for the help so far, I have another insite:

SELECT DISTINCT [ICILOC].[ITEMNO] as reregister, sum([ICILOC].[QTYONHAND]) as raster
FROM ICILOC
GROUP BY [ICILOC].[QTYONHAND],[ICILOC].[ITEMNO]
ORDER BY [ICILOC].[ITEMNO]

returns:
100CAB                .0000
100CAB                35.0000
100EXPBCAB        9.0000      
100EXPCAB          .0000
100EXPCAB           33.0000
100EXPPACK        .0000      

So it looks like it wont sum zero entries...
0
 
LVL 23

Author Comment

by:debuggerau
ID: 24248003
I've added this after the GROUP BY clause and it seems to have helped:
HAVING [ICILOC].[QTYONHAND] > 0

But now it disregards all entries without any quantity, maybe this is something I'll have to live with..
0
 
LVL 23

Author Comment

by:debuggerau
ID: 24248041
Unfortunately, that didn't do it either, I get the following:
DXLCD24BC               84.0000      3600.0000      1.0000
DXLCD24BC               84.0000      3600.0000      1372.0000

So it isn't adding all entries still, might need some sleep first..
0
 
LVL 4

Expert Comment

by:bleach77
ID: 24248170
How about this?
SELECT [ICILOC].[ITEMNO],[ICILOC].[LOCATION], sum([ICILOC].[QTYONHAND]) as raster
FROM ICILOC
GROUP BY [ICILOC].[ITEMNO],[ICILOC].[LOCATION]
ORDER BY [ICILOC].[ITEMNO]

Open in new window

0
 
LVL 23

Author Comment

by:debuggerau
ID: 24256073
Thanks for the suggestion, it returns:

100CAB                  1           35.0000      
100CAB                  12          .0000      
100CAB                  15          .0000      
100CAB                  2           .0000      
100CAB                  6           .0000      
100EXPBCAB          1           9.0000      
100EXPCAB            1           33.0000      

Which lists all the entries, not totaling them. I was hoping for:

100CAB                  1           35.0000      
100EXPBCAB          1           9.0000      
100EXPCAB            1           33.0000      

0
 
LVL 23

Author Comment

by:debuggerau
ID: 24256448
Been looking into a derived table for this one...
Any comments?

SELECT ITEMNO,SUM(sub.x)
FROM (
SELECT SUM([QTYONHAND]) AS x, ITEMNO
FROM [ICILOC]
WHERE [QTYONHAND] > 0
AND USED ='1'
GROUP BY [QTYONHAND],ITEMNO
) sub
GROUP BY ITEMNO
0
 
LVL 23

Author Comment

by:debuggerau
ID: 24256604
ok, the query above seems to do it exactly, without repeats and sum'ed it up nicely..

Now i just need to incorporate it into the first query for the complete listing.

Have tries this, but just keeps complaining..

SELECT [ICITEM].[ITEMNO], ISNULL(SUM([OESHHD].[QTYSOLD]), 0) AS TTlQtySold, ISNULL(SUM([PORCPL].[SQRECEIVED]), 0) AS TTlQtyRecieved, ISNULL(SUM(ditems.results), 0) AS TTlQtyInventory
FROM ICITEM  
LEFT JOIN OESHHD ON ITEM = ITEMNO AND [YR] = '2009' and [PERIOD] = '9'
LEFT JOIN PORCPL ON [PORCPL].[ITEMNO] = [ICITEM].[ITEMNO] AND [DTARRIVAL] < '20090331' and [DTARRIVAL] > '20090301'
LEFT JOIN  
(SELECT ITEMNO,SUM(sub.x) as result
FROM (
SELECT SUM([QTYONHAND]) AS x, ITEMNO
FROM [ICILOC]
WHERE [QTYONHAND] > 0
AND USED ='1'
GROUP BY [QTYONHAND],ITEMNO
) sub
GROUP BY ITEMNO) as ditems
ON [ICILOC].[ITEMNO] = [ICITEM].[ITEMNO]
GROUP BY [ICITEM].[ITEMNO], ITEM, QTYONHAND
ORDER BY [ICITEM].[ITEMNO]


Returns:
Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'ICILOC' does not match with a table name or alias name used in the query.

Has anyone got a clue, please?
0
 
LVL 23

Author Comment

by:debuggerau
ID: 24256650
this looks promising...

SELECT [ICITEM].[ITEMNO], ISNULL(SUM([OESHHD].[QTYSOLD]), 0) AS TTlQtySold, ISNULL(SUM([PORCPL].[SQRECEIVED]), 0) AS TTlQtyRecieved, ISNULL(SUM(sub.result), 0) AS TTlQtyInventory
FROM ICITEM  
LEFT JOIN OESHHD ON ITEM = ITEMNO AND [YR] = '2009' and [PERIOD] = '9'
LEFT JOIN PORCPL ON [PORCPL].[ITEMNO] = [ICITEM].[ITEMNO] AND [DTARRIVAL] < '20090331' and [DTARRIVAL] > '20090301'
LEFT OUTER JOIN  
(SELECT ITEMNO,SUM(sub.x) as result
FROM (
SELECT SUM([QTYONHAND]) AS x, ITEMNO
FROM [ICILOC]
WHERE [QTYONHAND] > 0
AND USED ='1'
GROUP BY [QTYONHAND],ITEMNO
) sub
GROUP BY ITEMNO) sub
ON [sub].[ITEMNO] = [ICITEM].[ITEMNO]
GROUP BY [ICITEM].[ITEMNO], ITEM
ORDER BY [ICITEM].[ITEMNO]
0
 
LVL 23

Accepted Solution

by:
debuggerau earned 0 total points
ID: 24257734
in the end:


SELECT [ICITEM].[ITEMNO], ISNULL(SUM([OESHHD].[QTYSOLD]), 0) AS TTlQtySold, ISNULL(suby.yresult, 0) AS TTlQtyRecieved, ISNULL(sub.result, 0) AS TTlQtyInventory
FROM ICITEM  
LEFT OUTER JOIN OESHHD ON ITEM = ITEMNO AND [YR] = '2009' and [PERIOD] = '8'
LEFT OUTER JOIN
(SELECT ITEMNO,SUM(suby.y) as yresult
FROM (
SELECT SUM([SQRECEIVED]) AS y, ITEMNO
FROM [PORCPL]
WHERE [SQRECEIVED] > 0
AND [DTARRIVAL] < '20090229' and [DTARRIVAL] > '20090201'
GROUP BY [SQRECEIVED],ITEMNO
) suby
GROUP BY ITEMNO) suby
ON [suby].[ITEMNO] = [ICITEM].[ITEMNO]
LEFT OUTER JOIN  
(SELECT ITEMNO,SUM(sub.x) as result
FROM (
SELECT SUM([QTYONHAND]) AS x, ITEMNO
FROM [ICILOC]
WHERE [QTYONHAND] > 0
AND USED ='1'
GROUP BY [QTYONHAND],ITEMNO
) sub
GROUP BY ITEMNO) sub
ON [sub].[ITEMNO] = [ICITEM].[ITEMNO]
GROUP BY [ICITEM].[ITEMNO], ITEM, sub.result, suby.yresult
ORDER BY [ICITEM].[ITEMNO]

Thanks everyone, hope this helps others too...
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

834 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