case statement giving error - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

case statement giving error

Author

Topic

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-05-19 : 14:47:26

Hi all,

I want to create sum of prices such that if user is retail customer and item is in sale then sale price is added to total else retail price is added in case of retail customer. I am getting error in case statement.

Error is:

Msg 8120, Level 16, State 1, Procedure ShoppingCartGetTotalAmount, Line 17
Column 'Products.onSale' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

ALTER PROCEDURE [dbo].[ShoppingCartGetTotalAmount]
(
@CartID char(36),
@userID int,
@IsWholesaler bit
)
AS

declare @TotalAmount money
set @TotalAmount = 0

if @IsWholesaler = 0

SELECT @TotalAmount = case p.onSale when 0 then ISNULL(SUM(p.UnitPrice_retail * s.Quantity), 0) else ISNULL(SUM(p.SalePrice * s.Quantity), 0) end
FROM ShoppingCart s INNER JOIN Products p
ON s.ProductID = p.ProductID
WHERE s.CreatedBy = @UserId

else

SELECT @TotalAmount = ISNULL(SUM(p.UnitPrice_WholeSale * s.Quantity), 0)
FROM ShoppingCart s INNER JOIN Products p
ON s.ProductID = p.ProductID
WHERE s.CreatedBy = @UserId

SELECT @TotalAmount

I have tried to use following but same error:

if @IsWholesaler = 0

SELECT @TotalAmount = case p.onSale
when 0 then (ISNULL(SUM(p.UnitPrice_retail * s.Quantity), 0) )
when 1 then (ISNULL(SUM(p.SalePrice * s.Quantity), 0)) end
FROM ShoppingCart s INNER JOIN Products p
ON s.ProductID = p.ProductID
WHERE s.CreatedBy = 1

Please help me on this

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-19 : 15:07:02

I'm not sure I'm following. But, maybe this will help you:

-- Set up sample data
DECLARE @ShoppingCart TABLE (ProductID INT, Quantity INT)
DECLARE @Products TABLE (ProductID INT, UnitPrice_retail MONEY, SalePrice MONEY, OnSale BIT)

INSERT @ShoppingCart
SELECT 1, 5
UNION ALL SELECT 2, 10
UNION ALL SELECT 3, 7

INSERT @Products
SELECT 1, $5.00, $4.00, 1
UNION ALL SELECT 2, $8.00, $4.00, 0
UNION ALL SELECT 3, $99.99, $80.00, 1

-- Run Select
SELECT
	SUM
	(
		CASE 
			WHEN p.onSale = 0 
				THEN COALESCE(p.UnitPrice_retail * s.Quantity, 0) 
			WHEN p.onSale = 1 
				THEN COALESCE(p.SalePrice * s.Quantity, 0) 
			ELSE 0 
		END
	) AS TotalAmount
FROM @ShoppingCart s 
INNER JOIN @Products p
ON s.ProductID = p.ProductID

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-05-19 : 15:25:51

thank you very much for your answer. It worked ! thank you again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources