• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 333
  • Last Modified:

Howdo sum entries for this JOINED column

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
debuggerau
Asked:
debuggerau
  • 11
  • 3
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
 
debuggerauAuthor Commented:
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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
debuggerauAuthor Commented:
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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
In the query shown above, it has only two columns selected and in the result set there are 3 columns. Kindly confirm.
0
 
debuggerauAuthor Commented:
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
 
debuggerauAuthor Commented:
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
 
debuggerauAuthor Commented:
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
 
debuggerauAuthor Commented:
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
 
bleach77Commented:
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
 
debuggerauAuthor Commented:
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
 
debuggerauAuthor Commented:
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
 
debuggerauAuthor Commented:
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
 
debuggerauAuthor Commented:
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
 
debuggerauAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 11
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now