CODEWITHSUNDEEP
sql-server
ScottyDooZA
ScottyDooZA

Reputation: 55

SUM of all values over a certain amount

The question is: Create a script that will calculate and print the total sum of all purchases.

The marking rubric also says that we must use DECLARE and IF statements in our answers

The output must look like this:

Sample Results:

(2 row(s) affected )

SUM OF EXPENSIVE PRODUCTS: 5998.00

This is a for a school assignment so I don't expect a full answer, just a push in the right direction as I don't know why this won't work.

I'm using SQL Server Management Studio 2014

This is what I've got so far:

DECLARE @SUM_OF_EXPENSIVE_PRODUCTS int, @SUM_OF_AVERAGE_PRODUCTS int, @SUM_OF_CHEAP_PRODUCTS int

IF ((SELECT Price AS EXPENSIVE_PRODUCT FROM Product WHERE Price > 2000 ) > 0)
    SELECT SUM(EXPENSIVE_PRODUCT) = @SUM_OF_EXPENSIVE_PRODUCTS

ELSE IF ((SELECT Price AS AVERAGE_PRODUCT FROM Product WHERE Price > 1000) > 0)
    SELECT SUM(AVERAGE_PRODUCT) = @SUM_OF_AVERAGE_PRODUCTS

ELSE IF ((SELECT Price AS CHEAP_PRODUCT FROM Product WHERE Price < 1000) > 0)
    SELECT SUM(CHEAP_PRODUCT) = @SUM_OF_CHEAP_PRODUCTS

ELSE 
    SELECT 'NO PRODUCTS EXIST'

Upvotes: 3

Views: 314

Answers (1)

RaJesh RiJo
RaJesh RiJo

Reputation: 4400

Just try this,

Updated Query

BEGIN
    DECLARE @ResultText varchar(100)
    DECLARE @Result varchar(10)
    IF EXISTS(Select 1 from tempTable WHERE Price > 1999)
    BEGIN
        SET @ResultText = 'SUM_OF_EXPENSIVE_PRODUCTS : '
        SET @Result = (SELECT SUM(Price) from tempTable where Price > 1999)
        SET @ResultText = @ResultText + @Result
    END
    ELSE IF EXISTS(Select 1 from tempTable WHERE Price > 999)
    BEGIN 
        SET @ResultText = 'SUM_OF_AVERAGE_PRODUCTS : '
        SET @Result = (SELECT SUM(Price) from tempTable where Price > 999)
        SET @ResultText = @ResultText + @Result
    END
    ELSE IF EXISTS(Select 1 from tempTable WHERE Price > 0)
    BEGIN
        SET @ResultText = 'SUM_OF_AVERAGE_PRODUCTS : '
        SET @Result = (SELECT SUM(Price) from tempTable where Price > 0)
        SET @ResultText = @ResultText + @Result
    END
    ELSE
    BEGIN
        SET @ResultText = 'NO PRODUCTS EXIST'       
    END
    SELECT @ResultText
END

Upvotes: 1

Related Questions