parameters with multiple values and summing them for a bar chart

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

Answer 1

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

Answer 2

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 ).

Answer 3

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