Sum values with an If?

  • I need to generate a report from a table using the values from a filed to determine what to add up.

    create table fun_test(

    packtype nvarchar(50),

    price money)

    insert into fun_test

    values ('Single',''),('Monthly','25.00'),('Monthly','27.00'),('Monthly','23.50'),('Single',''),('Deposit',''),('Deposit','')

    select PackType,count(*) as Records,sum(price) as Value

    from fun_test

    group by PackType

    order by PackType

    What I need is if the PackType is Deposit and need to multiply the number of records by 35, If the PackType is Monthly I need to multiply the records by 25 and if the PackType is Single I need to sum the values in the price column. Can anyone suggest a good way to do this?

  • Quick suggestion, use a case statement

    😎

    USE tempdb;

    GO

    create table dbo.fun_test(

    packtype nvarchar(50),

    price money)

    insert into dbo.fun_test

    values ('Single','2'),('Monthly','25.00'),('Monthly','27.00'),('Monthly','23.50'),('Single','2'),('Deposit','1'),('Deposit','2')

    select

    PackType

    ,count(*) as Records

    ,sum(CASE

    WHEN PackType = 'Deposit' THEN 35.0

    WHEN PackType = 'Monthly' THEN 25.0

    ELSE 1 END * price) as Value

    from dbo.fun_test

    group by PackType

    order by PackType

    DROP TABLE dbo.fun_test;

    Results

    PackType Records Value

    ----------- ----------- ------------

    Deposit 2 105.00000

    Monthly 3 1887.50000

    Single 2 4.00000

  • Great - I've adapted your statement, but using the case has solved the issue! Thanks

    I worded my original query slightly wrong too! Shoudl have said single=25, deposit=35, monthly=value in price column

    select PackType

    ,count(*) as Records

    ,cast(sum(CASE

    WHEN PackType in ('Single') THEN 25

    WHEN PackType in ('Deposit') THEN 35

    ELSE price END) as Money) as Value

    from dbo.fun_test

    group by PackType

    order by PackType

  • bicky1980 (10/28/2014)


    Great - I've adapted your statement, but using the case has solved the issue! Thanks

    I worded my original query slightly wrong too! Shoudl have said single=25, deposit=35, monthly=value in price column

    select PackType

    ,count(*) as Records

    ,cast(sum(CASE

    WHEN PackType in ('Single') THEN 25

    WHEN PackType in ('Deposit') THEN 35

    ELSE price END) as Money) as Value

    from dbo.fun_test

    group by PackType

    order by PackType

    Assuming you're only dealing with one or 2 values and they don't change, the CASE is fine; otherwise you'd probably want to consider have a PackTypes table with the appropriate values.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Rather than use a CASE statement, I would create a new table for the multipliers and then join them to the PackType table. If this is to be used in a single query or stored procedure then create it as an in-memory @Table; if you need to refer to in in a number of places, create it as a permanent table.

    DECLARE @Multiplier AS TABLE

    (

    MType NVARCHAR(30)

    MValue DECIMAL(10,2)

    )

    INSERT INTO @Multiplier (MType,MValue) VALUES ('Monthly', 25)

    INSERT INTO @Multiplier (MType,MValue) VALUES ('Deposit', 35)

    select FT.PackType,count(FT.PackType) as Records,sum(FT.price * M.MValue) as Value

    from fun_test FT

    join @Multiplier M ON M.MType = FT.PackType

    group by FT.PackType

    order by FT.PackType

    This keeps the main query clean and makes maintenace much easier because all you need to do is edit the insert statements to modify records.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply