Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Sum Functions returns Twice?..

Author  Topic 

adbasanta
Posting Yak Master

120 Posts

Posted - 2013-02-02 : 19:56:33
Good day!

I have this running query that sums up the quantity of an items base on the date criteria. However Ive notice that the result has been displayed twice. If I change the red color below to this:

SUM(BeginInventory) AS BeginInventory,
SUM(Purchases) as Purchases,
SUM(TransfersIn) AS TransfersIn,
SUM(TransfersOut) AS TransfersOut,
SUM(SoldItem) AS SoldItem



If I put Sum on the aggregate variables it doubles the quantity. Which means that result is supposed to be 46 but it displays 92. At the same time it appears twice too which is not correct.
The result of the above changed is this:

productcode BeginInventory itemname Purchases TransfersIn TransfersOut SoldItem
4567342200 0 cream milk 92 0 0 36
4567342200 0 cream milk 92 0 0 36

The result of summing the quantity of an item must only be appear once per item. Ive marked red those that I think is the problem or maybe its the LEFT JOIN? Here is my code:

USE [POS_DB]
GO
/****** Object: StoredProcedure [dbo].[Show_all_items_DateView] Script Date: 01/24/2013 23:31:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Show_all_items_DateView]
-- Add the parameters for the stored procedure here

@fromdate as DATETIME,
@todate as DATETIME

As
Begin

SELECT p.productcode,
itemname,
description,
packtype,
itemtype,
c.categoryname,
s.suppliername,
supplierprice,
srp,
discount,
itemlocation,
expdate,
vatable,
service_other_charge_amount_percent,
COALESCE(BeginInventory,0) AS BeginInventory,
COALESCE(Purchases,0) as Purchases,
COALESCE(TransfersIn,0) AS TransfersIn,
COALESCE(TransfersOut,0) AS TransfersOut,
COALESCE(SoldItem,0) AS SoldItem

COALESCE(Sold,0) AS RunningOnhand

FROM tbl_product_list p

LEFT JOIN (SELECT prodcode,
SUM(CASE WHEN memo='beginning' THEN qty ELSE 0 END) AS BeginInventory,
SUM(CASE WHEN memo='purchases' THEN qty ELSE 0 END) AS Purchases

FROM PO_detail
WHERE podate >= @fromdate AND podate < @todate + 1
GROUP BY prodcode,memo)i
ON i.prodcode = p.productcode

LEFT JOIN (SELECT prodcode,
SUM(CASE WHEN TransferInOutMemo = 'IN' THEN qty END) AS TransfersIn,
SUM(CASE WHEN TransferInOutMemo = 'OUT' THEN qty END) AS TransfersOut

FROM tbl_product_transfer_detail
WHERE transdate >= @fromdate AND transdate < @todate + 1
GROUP BY prodcode,TransferInOutMemo,qty)pts
ON pts.prodcode= p.productcode

LEFT JOIN (SELECT productcode,SUM(qty) AS SoldItem
FROM tbl_pos_sales_detail
WHERE transdate >= @fromdate AND transdate < @todate + 1
GROUP BY productcode)psd
ON psd.productcode= p.productcode

LEFT JOIN tbl_supplier s
ON s.suppliername=(SELECT s.suppliername
WHERE s.suppliercode=p.supplierid)

LEFT JOIN tbl_prod_category c
ON c.categoryname=(SELECT c.categoryname
WHERE c.catgoryid=p.category)


GROUP BY p.productcode,itemname,description,packtype,itemtype,c.categoryname,
s.suppliername,supplierprice,srp,discount,itemlocation,expdate,service_other_charge_amount_percent,vatable,BeginInventory,Purchases,SoldItem,
TransfersIn,TransfersOut
ORDER BY p.itemname ASC
End


Thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-02-02 : 22:35:53
Looks like in your two derived tables you need to change the GROUP BY to just prodcode (and productcode for the other one). Both those derived tables are potentially returning multiple produce code rows. Also I doubt you need the GROUP BY in the main query.

Be One with the Optimizer
TG
Go to Top of Page

adbasanta
Posting Yak Master

120 Posts

Posted - 2013-02-03 : 02:06:24
thank you TG!

Ive changed the derived query to this but still the same result.

LEFT JOIN (SELECT prodcode,
SUM(CASE WHEN memo='beginning' THEN qty ELSE 0 END) AS BeginInventory,
SUM(CASE WHEN memo='purchases' THEN qty ELSE 0 END) AS Purchases
FROM PO_detail
WHERE podate >= @fromdate AND podate < @todate + 1
GROUP BY prodcode)i
ON i.prodcode = p.productcode

LEFT JOIN (SELECT prodcode,
SUM(CASE WHEN TransferInOutMemo = 'IN' THEN qty END) AS TransfersIn,
SUM(CASE WHEN TransferInOutMemo = 'OUT' THEN qty END) AS TransfersOut
FROM tbl_product_transfer_detail
WHERE transdate >= @fromdate AND transdate < @todate + 1
BY prodcode)pts
ON pts.prodcode= p.productcode

LEFT JOIN (SELECT productcode,SUM(qty) AS SoldItem
FROM tbl_pos_sales_detail
WHERE transdate >= @fromdate AND transdate < @todate + 1
GROUP BY productcode)psd
ON psd.productcode= p.productcode


Thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-03 : 06:37:14
One way to debug this would be to take one table at a time and see how many rows are returned for the product code of interest (4567342200). So start with tbl_product_list
SELECT * FRM tbl_product_list WHERE productcode = 4567342200;
This should return only one row. Do that with the second table - and you should still get only one row. Now do that with the third table
SELECT prodcode,
SUM(CASE WHEN TransferInOutMemo = 'IN' THEN qty END) AS TransfersIn,
SUM(CASE WHEN TransferInOutMemo = 'OUT' THEN qty END) AS TransfersOut
FROM tbl_product_transfer_detail
WHERE transdate >= @fromdate AND transdate < @todate + 1
AND prodcode = 4567342200
GROUP BY prodcode,TransferInOutMemo,qty
You may get more than one row. If that is the case, that means you have more than combination of TransferInOutMemo/qty. If that is the case, you have to figure out how you want to handle that. Perhaps you should group only by prodcode ?
Go to Top of Page

adbasanta
Posting Yak Master

120 Posts

Posted - 2013-02-03 : 10:49:12
Thank you James K!

Upon checking, this 3 tables returns twice or more PO_detail, tbl_product_transfer_detail, tbl_pos_sales_detail. This is I think normal because PO_detail will hold all the detail of items purchased. bl_product_transfer_detail will hold all the detail of items being transfered. And tbl_pos_sales_detail will hold all the detail of items being tsold. I think I missed the point here. Maybe my left joins in the query?

Thank you!



-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-03 : 10:57:51
can you post how sample data is for a particular po? Is transfer recorded as a single or multiple records?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-03 : 18:40:22
What I was suggesting was that inferring from your SELECT statements in the third join, it seems like you would not need to group by TransferInOutMemo,qty. See change below. Give that a try and see if that gets you close to what you are looking for:
         LEFT JOIN (SELECT prodcode,
SUM(CASE WHEN TransferInOutMemo = 'IN' THEN qty END) AS TransfersIn,
SUM(CASE WHEN TransferInOutMemo = 'OUT' THEN qty END) AS TransfersOut
FROM tbl_product_transfer_detail
WHERE transdate >= @fromdate AND transdate < @todate + 1
GROUP BY prodcode ,TransferInOutMemo,qty )pts
ON pts.prodcode= p.productcode

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-03 : 23:02:53
But as per OPS post 02/03/2013 : 02:06:24 it seems even after doing the changes he's getting duplicates

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

adbasanta
Posting Yak Master

120 Posts

Posted - 2013-02-06 : 06:12:38
thank you visakh16, James K, TG!

I still have this problem. Sorry for late reply. Anyway here's my table structure. Hope you guys can help me to solve this:

PO_detail

porefno podate supplierid prodcode itemname qty memo
000001 2/2/2013 11 4567342200 cream milk 23 purchases
000001 2/2/2013 11 4567342201 dairy cream 16 purchases

000002 2/6/2013 11 4567342200 cream milk 12 purchases
000003 2/6/2013 11 4567342201 dairy cream 4 beginning

.....and so on...


Thank you for helping!

tbl_product_transfer_detail

transref ptransdate supplierid prodcode itemname TransferInOutMemo qty
1 2/2/2013 11 4567342200 cream milk IN 6
1 2/2/2013 11 4567342201 dairy cream OUT 5

2 2/6/2013 11 4567342200 cream milk IN 3
2 2/6/2013 11 4567342201 dairy cream OUT 4

.....and so on...


tbl_pos_sales_detail

salesid transdate prodcode productdesc qty salestype
00000001 2/2/2013 4567342200 cream milk 4 cash
00000001 2/2/2013 4567342201 dairy cream 7 cash

00000002 2/6/2013 4567342200 cream milk 3 credit
00000002 2/6/2013 4567342201 dairy cream 2 credit

.....and so on...


Thank you for helping!
-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-06 : 08:24:51
To continue with the thought process that I was describing in my previous post, now you have verified that the first 2 joins should give you only one row. It seems to me like the third join also should do the same - i.e., still give you only one row. That leaves the fourth and fifth joins. See if you have more than one row for a given suppliername in tbl_supplier or more than one row for a given categoryname in tbl_prod_category.

Also, the last two joins seem somewhat unusual/unnecessary. Try changing those to:
LEFT JOIN  tbl_supplier s
ON s.suppliername=p.supplierid

LEFT JOIN tbl_prod_category c
ON c.categoryname=p.category
Go to Top of Page

adbasanta
Posting Yak Master

120 Posts

Posted - 2013-02-08 : 20:36:55
Thank you James K, visakh16!

Ive modified the query and removed the last two LEFT JOINS but I still have this duplicate results. Here is the modified one:


USE [POS_DB]
GO
/****** Object: StoredProcedure [dbo].[Show_all_items_DateView] Script Date: 01/24/2013 23:31:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Show_all_items_DateView]
-- Add the parameters for the stored procedure here

@fromdate as DATETIME,
@todate as DATETIME

As
Begin

SELECT p.productcode,
itemname,
description,
packtype,
itemtype,
c.categoryname,
s.suppliername,
supplierprice,
srp,
discount,
itemlocation,
expdate,
vatable,
service_other_charge_amount_percent,
COALESCE(BeginInventory,0) AS BeginInventory,
COALESCE(Purchases,0) as Purchases,
COALESCE(TransfersIn,0) AS TransfersIn,
COALESCE(TransfersOut,0) AS TransfersOut,
COALESCE(SoldItem,0) AS SoldItem
--COALESCE(BeginInventory,0) + COALESCE(Purchases,0) + COALESCE(TransfersIn,0) - COALESCE(TransfersOut,0) - COALESCE(Sold,0) AS RunningOnhand

FROM tbl_product_list p

LEFT JOIN (SELECT prodcode,
SUM(CASE WHEN memo='beginning' THEN qty ELSE 0 END) AS BeginInventory,
SUM(CASE WHEN memo='purchases' THEN qty ELSE 0 END) AS Purchases
FROM PO_detail
WHERE podate >= @fromdate AND podate < @todate + 1
GROUP BY prodcode,memo)i
ON i.prodcode = p.productcode

LEFT JOIN (SELECT prodcode,
SUM(CASE WHEN TransferInOutMemo = 'IN' THEN qty END) AS TransfersIn,
SUM(CASE WHEN TransferInOutMemo = 'OUT' THEN qty END) AS TransfersOut
FROM tbl_product_transfer_detail
WHERE transdate >= @fromdate AND transdate < @todate + 1
GROUP BY prodcode,TransferInOutMemo,qty)pts
ON pts.prodcode= p.productcode

LEFT JOIN (SELECT productcode,SUM(qty) AS SoldItem
FROM tbl_pos_sales_detail
WHERE transdate >= @fromdate AND transdate < @todate + 1
GROUP BY productcode)psd
ON psd.productcode= p.productcode


GROUP BY p.productcode,itemname,description,packtype,itemtype
,supplierprice,srp,discount,itemlocation,expdate,service_other_charge_amount_percent,vatable,BeginInventory,Purchases,SoldItem,
TransfersIn,TransfersOut
ORDER BY p.itemname ASC
End


Now I got this result

productcode BeginInventory itemname Purchases TransfersIn TransfersOut SoldItem
4567342200 0 cream milk 1400 0 0 493
4567342200 0 cream milk 1400 0 0 599


The first result is correct as Ive check in in its corresponding table. However the second row(color red) should no be displayed because its just the same item. And I also wonder that in the second row the qty is not the same in the first row?

Thank you!


-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-08 : 23:15:32
The last posted statement will definitely bring duplicate results as you're grouping on other fields like TransferInOutMemo which will ahve unique values within a group causing duplicates

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

adbasanta
Posting Yak Master

120 Posts

Posted - 2013-02-08 : 23:26:32
thank you visakh16!

Noted and changed but still I have duplicated results?


LEFT JOIN (SELECT prodcode,
SUM(CASE WHEN TransferInOutMemo = 'IN' THEN qty END) AS TransfersIn,
SUM(CASE WHEN TransferInOutMemo = 'OUT' THEN qty END) AS TransfersOut
FROM tbl_product_transfer_detail
WHERE transdate >= @fromdate AND transdate < @todate + 1
GROUP BY prodcode)pts


Thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008
Go to Top of Page

adbasanta
Posting Yak Master

120 Posts

Posted - 2013-02-08 : 23:37:37
I think this might affect duplicate results?

My table structure for tbl_pos_sales_detail will have the same salesid but with different salestype='cash' or 'credit'. Just like below.

salesid transdate prodcode productdesc qty salestype
00000001 2/2/2013 4567342200 cream milk 4 cash
00000001 2/2/2013 4567342201 dairy cream 7 cash

00000002 2/6/2013 4567342200 cream milk 3 credit
00000002 2/6/2013 4567342201 dairy cream 2 credit

00000001 2/2/2013 4567342200 cream milk 3 credit
00000001 2/2/2013 4567342201 dairy cream 1 credit


Thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-08 : 23:45:04
Nope still if you're grouping on prodcode you should get only one row per prodcode

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

adbasanta
Posting Yak Master

120 Posts

Posted - 2013-02-09 : 01:17:15
Thank you visakh16!

It seems that the query is correct. But I still cant figure out which part is missing on my table structure!
Which part should I execute checking?

Thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-09 : 01:22:29
i think issue is with tbl_product_list table containing duplicates

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

adbasanta
Posting Yak Master

120 Posts

Posted - 2013-02-09 : 01:31:33
thank you visakh16!

I'll check it out..

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008
Go to Top of Page

adbasanta
Posting Yak Master

120 Posts

Posted - 2013-02-11 : 03:02:20
Thank you visakh16!

I have already done checking duplicate records on tbl_product_list but there is no duplicate records as Ive checked using this query:

use POS_DB
go
select productcode, count(productcode) as NumOccurences
from tbl_product_list
group by productcode
having (COUNT(productcode) > 1)


However Ive noticed that even when I cut the query with just below:


USE [POS_DB]
GO
/****** Object: StoredProcedure [dbo].[Show_all_items_DateView] Script Date: 01/24/2013 23:31:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Show_all_items_DateView]
-- Add the parameters for the stored procedure here

@todate as DATETIME

As
Begin

SELECT p.productcode,
itemname,
COALESCE(BeginInventory,0) AS BeginInventory,
COALESCE(Purchases,0) as Purchases

FROM tbl_product_list p

LEFT JOIN (SELECT prodcode,
SUM(CASE WHEN memo='beginning' THEN qty ELSE 0 END) AS BeginInventory,
SUM(CASE WHEN memo='purchases' THEN qty ELSE 0 END) AS Purchases
FROM PO_detail
WHERE podate < @todate + 1
GROUP BY prodcode)i
ON i.prodcode = p.productcode

GROUP BY p.productcode,itemname,BeginInventory,Purchases


I still have duplicate results like this:

productcode itemname BeginInventory Purchases
4567342200 cream milk 0 186
4567342200 cream milk 0 792

The first row should not be displayed!


Here is again my PO_detail table structure:

porefno podate supplierid prodcode itemname qty memo
000001 2/2/2013 11 4567342200 cream milk 23 purchases
000001 2/2/2013 11 4567342201 dairy cream 16 purchases

000002 2/6/2013 11 4567342200 cream milk 12 purchases
000003 2/6/2013 11 4567342201 dairy cream 4 beginning

000004 2/8/2013 11 4567342200 dairy cream 2 void

Thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-11 : 03:24:39
the issue is your group by. try this


USE [POS_DB]
GO
/****** Object: StoredProcedure [dbo].[Show_all_items_DateView] Script Date: 01/24/2013 23:31:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Show_all_items_DateView]
-- Add the parameters for the stored procedure here

@todate as DATETIME

As
Begin

SELECT p.productcode,
itemname,
COALESCE(SUM(BeginInventory),0) AS BeginInventory,
COALESCE(SUM(Purchases),0) as Purchases

FROM tbl_product_list p

LEFT JOIN (SELECT prodcode,
SUM(CASE WHEN memo='beginning' THEN qty ELSE 0 END) AS BeginInventory,
SUM(CASE WHEN memo='purchases' THEN qty ELSE 0 END) AS Purchases
FROM PO_detail
WHERE podate < @todate + 1
GROUP BY prodcode)i
ON i.prodcode = p.productcode

GROUP BY p.productcode,itemname,BeginInventory,Purchases


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

adbasanta
Posting Yak Master

120 Posts

Posted - 2013-02-11 : 03:41:48
thank you visakh16!

Ive changed the query to this but Ive noticed that the quantity is not correct.
 
USE [POS_DB]
GO
/****** Object: StoredProcedure [dbo].[Show_all_items_DateView] Script Date: 01/24/2013 23:31:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Show_all_items_DateView]
-- Add the parameters for the stored procedure here

@todate as DATETIME

As
Begin

SELECT p.productcode,
itemname,
COALESCE(SUM(BeginInventory),0) AS BeginInventory,
COALESCE(SUM(Purchases),0) as Purchases

FROM tbl_product_list p

LEFT JOIN (SELECT prodcode,
SUM(CASE WHEN memo='beginning' THEN qty ELSE 0 END) AS BeginInventory,
SUM(CASE WHEN memo='purchases' THEN qty ELSE 0 END) AS Purchases
FROM PO_detail
WHERE podate < @todate + 1
GROUP BY prodcode)i
ON i.prodcode = p.productcode

GROUP BY p.productcode,itemname


Now it shows only one result. However the quantity is not correct. Base on this duplicate result:

productcode itemname BeginInventory Purchases
4567342200 cream milk 0 186
4567342200 cream milk 0 792


It now becomes this:


productcode itemname BeginInventory Purchases
4567342200 cream milk 0 978

It adds the qty of first and second row on the first duplicate results. Which is not supposed to be.

Thank you!


-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008
Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -