asked on

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.

Raja Jegan R

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

debuggerau

ASKER

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

Any other suggestions?

Raja Jegan R

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

debuggerau

ASKER

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...

Raja Jegan R

In the query shown above, it has only two columns selected and in the result set there are 3 columns. Kindly confirm.

debuggerau

ASKER

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]

debuggerau

ASKER

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...

debuggerau

ASKER

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..

debuggerau

ASKER

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..

bleach77

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

debuggerau

ASKER

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

debuggerau

ASKER

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

debuggerau

ASKER

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?

debuggerau

ASKER

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]

ASKER CERTIFIED SOLUTION

debuggerau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial