locked
parameters with multiple values and summing them for a bar chart RRS feed

  • Question

  • User632751675 posted

    hi,

    I have a query that takes in multiple parameters, some of which can have many values.

    I know that I can check for the values in the where clause : where carType = (@cartype),

    but what if I want to sum the value of cartype ?    Should I create a temporary table and then loop through it to sum up and display all the

    different values?  I'm not sure what the best and most efficient method is. 

    thanks

    Wednesday, March 9, 2016 12:00 AM

All replies

  • User-219423983 posted

    Hi maggiemays,

    but what if I want to sum the value of cartype ?    Should I create a temporary table and then loop through it to sum up and display all the different values?

    If you want to sum the value of “cartype”, why do you display all the different values? I guess you want to display the values of “cartype”. So, you could create a temporary table and then use

    Select sum(cartype) from [TempTable]

    to get the total value.

    If you want to get the results for the bar chart, you could create a Stored Procedure and use the Table valued type as a parameter. Then, in the SP, you could refer to the following KB article to get the multiple results from the SP.

    https://support.microsoft.com/en-us/kb/311274

    Best Regards,

    Weibo Zhang

    Wednesday, March 9, 2016 2:15 AM
  • User632751675 posted

    hi,

    I wanted to stay away from creating another stored procedure. 

    I want to display the sum value of each different cartype (  cartype convertible = 300, sedan = 100,etc ).

    Wednesday, March 9, 2016 4:16 PM
  • User-219423983 posted

    Hi maggiemays

    Could you share your table structure?

    As I’m not sure how your table be built, you could have a look at the following demo by using Group By and maybe it would helpful to you.

    declare @table table(cartypeName varchar(20),cartypeValue int)
    insert into @table values('convertible',300)
    insert into @table values('convertible',400)
    insert into @table values('convertible',600)
    insert into @table values('sedan',200)
    insert into @table values('sedan',100)
    insert into @table values('sedan',500)
    insert into @table values('XXX',100)
    insert into @table values('XXX',200)
    
    --Create the Temporary table to stored the multiple values
    declare @temp table(val varchar(20))
    insert into @temp values('convertible')
    insert into @temp values('sedan')
    
    select SUM(cartypeValue) as CountValue, cartypeName
    from @table
    where cartypeName in (select val from @temp)
    Group by cartypeName
    

    Best Regards,

    Weibo Zhang

    Thursday, March 10, 2016 2:02 AM