How to find out sum of a column with respect to another columns maximum and minimum

Question

I have worked with DB2, and I just moved to the SQL Server. I'm a bit confused by a query.

Lets suppose I have table data like

StoreID | Sales
   A    |  23
   B    |  50
   B    |  50

In this data with the stored procedure parameter, I wanted to roll up the sum of Sales. I will get a parameter of StoreID, but in this parameter I can get 'ALL' too.

In DB2 I can get all data using a stored procedure (which has StoreID in a parameter named ParameterStore) with a query like

if(ParameterStore= 'ALL') Then
Set Loc_min = X'00';
Set Loc_max = X'FF';
ELSE
Set Loc_min = ParameterStore;
Set Loc_max = ParameterStore;
END if;

Select Sum(Sales) From Store_data where StoreID between Loc_min and Loc_max;

How can I do this in SQL Server to get the same result?

Output is different for Different Scenario: if Parameter Passed has Value 'A' the output Will Be 23, If Parameter passsed is B then 100. and If parameter passed is 'ALL' Then 123. — Kshitij, Jan 01 '18 at 11:07

score 2 · Answer 1 · answered Jan 01 '18 at 10:15

2

You could check the value if it's ALL or some other value in OR:

DECLARE @store_id VARCHAR(20) -- or whatever length you have
SET @store_id = 'XYZ'

select sum(sales)
from store_data
where @store_id = 'ALL' or store_id = @store_id;

answered Jan 01 '18 at 10:15

Gurwinder Singh

38,557
6
51
76

How it will work i didn't get it. I don't have StoreID Stored as 'ALL' in table. So when i Pass 'ALL' In parameter how it get me sum of all the rows Sales? – Kshitij Jan 01 '18 at 11:15
**This might not work**. SQL Server may or may not short circuit the OR clause and you end up withan error such as _Conversion failed when converting the varchar value 'ALL' to data type int_. See https://stackoverflow.com/q/19429229/87015 – Salman A Jan 01 '18 at 12:44

score 0 · Answer 2 · answered Jan 01 '18 at 11:34

Try this if you need hexadecimal comparison.

DECLARE
  @ParameterStore VARCHAR(10)
  SET @ParameterStore = 'ALL'
  SELECT SUM(Sales)
  FROM Store_data
  WHERE StoreID BETWEEN (
    CASE
      WHEN @ParameterStore = 'ALL'
      THEN 0x00
      ELSE @ParameterStore
    END)
  AND (
    CASE
      WHEN @ParameterStore = 'ALL'
      THEN 0xFF
      ELSE @ParameterStore
    END ) ;

DEMO

Salman A · Accepted Answer · 2018-01-01T12:59:19.137

T-SQL syntax for IF is slightly different. You can use it like this:

/*
DECLARE @ParameterStore VARCHAR(10);
DECLARE @Loc_min INT;
DECLARE @Loc_max INT;
*/

IF @ParameterStore = 'ALL'
    BEGIN
        Set @Loc_min = 0x00;
        Set @Loc_max = 0xFF;
    END
ELSE
    BEGIN
        Set @Loc_min = @ParameterStore;
        Set @Loc_max = @ParameterStore;
    END;

Having said that, what you are trying to achieve could be done as follows:

-- DECLARE @ParameterStore VARCHAR(10);
SELECT SUM(Sales)
FROM Store_data
WHERE CASE
    WHEN @ParameterStore = 'ALL' THEN -1
    WHEN @ParameterStore = StoreID THEN -1
    END = -1

How to find out sum of a column with respect to another columns maximum and minimum

3 Answers3