January 20, 2009 at 4:53 am
Hello,
I have a two tables
1) Product - which contains fields like
ProductID(Primary key) , ProductName, ProductDetails, Rate, ProductCancelationFlag etc
2) ProductSales - which contains fieldslike
PSalesID(Primary key), ProductID(Foreign key), TotalUnits, TotalAmount, BalanceAmount, etc
now, i want the answer in format,
1)Productid, productname,
2)sum of total amount in ProductSales table related to that product,
3)and also sum of balance amount in Productsales related to that product
the conditions are
1) ProductCancelationFlag is not true.
(i.e. if product is cancelled do not include it.)
2) if sum(productsales.totalamount)<>0 and
sum(productsales.balanceamount)<>0
(i.e. if both sum are zero then do not include it.)
I have Tried like follows but it gives all records it is not skipping the 2nd condition (i.e. if both sum are zero then do not include it.)
My Query is:
SELECT DISTINCT
Product.ProductID, Product.ProductName, Product.Rate, SUM(ProductSales.TotalAmount) AS TotalAmount,
SUM(ProductSales.BalanceAmount) AS BalanceAmount
FROM ProductSales RIGHT OUTER JOIN
Product ON ProductSales.ProductID = Product.ProductID
WHERE (COALESCE (Product.ProductCancelFlag, 0) = 0)
GROUP BY Product.ProductID, Product.ProductName, Product.Rate
ORDER BY Product.ProductID
Please Help me.
Thanks & Regards,
Aditya
January 20, 2009 at 5:35 am
Try using the HAVING clause. Look it up in Books Online for details, but it would look something like this:
SELECT...
FROM...
WHERE..
GROUP BY...
HAVING SUM(MyValue) > 0
It's sort of like a WHERE clause for aggregate functions.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 20, 2009 at 5:36 am
Oh, and it goes before the ORDER BY clause.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 20, 2009 at 9:35 pm
Thanks Grant.
It's working.
Thanks & Regards,
Aditya
January 21, 2009 at 5:32 am
Great. Glad to hear it. And thanks for posting the success of the suggestion.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 21, 2009 at 6:10 am
I think below example will give you better performance ...
CREATE TABLE tmpProduct
(
ProductIDINT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
ProductNameVARCHAR(10)
)
CREATE TABLE tmpProductSales
(
ProductSalesIDINT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
ProductIDINT,
TotalAmtMONEY,
BalanceAmtMONEY
)
INSERTtmpProduct(ProductName)
SELECT'Pr0duct' UNION
SELECT'Pr0duct2' UNION
SELECT'Pr0duct3' UNION
SELECT'Pr0duct4'
INSERTtmpProductSales(ProductID, TotalAmt, BalanceAmt)
SELECT1, 0, 700 UNION
SELECT1, 200, 600 UNION
SELECT2, 300, 500 UNION
SELECT2, 400, 400 UNION
SELECT3, 500, 300 UNION
SELECT3, 600, 200 UNION
SELECT4, 700, 100 UNION
SELECT4, 0, 0
SELECTP.ProductID, P.ProductName, SUM(PS.TotalAmt) AS 'TotalAmt',
SUM(PS.BalanceAmt) AS 'BalanceAmt'
FROMtmpProduct P
JOINtmpProductSales PS ON P.ProductID = PS.ProductID
WHERE(PS.TotalAmt > 0 OR PS.BalanceAmt > 0)
--AND "your conditions"ProductCancelationFlag is not true
GROUP BY P.ProductID, P.ProductName
Abhijit - http://abhijitmore.wordpress.com
January 21, 2009 at 6:19 am
Abhijit More (1/21/2009)
I think below example will give you better performance ...CREATE TABLE tmpProduct
(
ProductIDINT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
ProductNameVARCHAR(10)
)
CREATE TABLE tmpProductSales
(
ProductSalesIDINT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
ProductIDINT,
TotalAmtMONEY,
BalanceAmtMONEY
)
INSERTtmpProduct(ProductName)
SELECT'Pr0duct' UNION
SELECT'Pr0duct2' UNION
SELECT'Pr0duct3' UNION
SELECT'Pr0duct4'
INSERTtmpProductSales(ProductID, TotalAmt, BalanceAmt)
SELECT1, 0, 700 UNION
SELECT1, 200, 600 UNION
SELECT2, 300, 500 UNION
SELECT2, 400, 400 UNION
SELECT3, 500, 300 UNION
SELECT3, 600, 200 UNION
SELECT4, 700, 100 UNION
SELECT4, 0, 0
SELECTP.ProductID, P.ProductName, SUM(PS.TotalAmt) AS 'TotalAmt',
SUM(PS.BalanceAmt) AS 'BalanceAmt'
FROMtmpProduct P
JOINtmpProductSales PS ON P.ProductID = PS.ProductID
WHERE(PS.TotalAmt > 0 OR PS.BalanceAmt > 0)
--AND "your conditions"ProductCancelationFlag is not true
GROUP BY P.ProductID, P.ProductName
While it may perform better without the HAVING clause, it won't provide the right data. Note the requirements:
2) if sum(productsales.totalamount)<>0 and
sum(productsales.balanceamount)<>0
(i.e. if both sum are zero then do not include it.)
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 21, 2009 at 9:47 pm
Hello Grant,
I have tried abhijit query also. It is also working.
I am new in sql. so i cant understand the difference.
when it will give wrong result.
Please explain me.
Thanks & Regards,
Aditya.
January 22, 2009 at 5:46 am
On another reading through the code, I think I'm going to eat crow and agree. That was a better approach and I'm wrong about it. I should have looked more closely at the code before I posted (a mistake I make far too frequently).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply