question

davidt0504 avatar image
davidt0504 asked

Trying to get the sum of specific values for specific files for specific software versions

I've got a table that has simulation data in it. Four of the columns are "Version", "Scenario_Name", "Measure", and "Value". The "Version" column will have the software version number in it. The "Scenario_Name" column will have the file names for the scenario in them. The "Measure" column will have essentially a data type entry for the scenario something like Time, Money, Costs, etc. The "Value" column will record the number that is associated with the Measure column. For example, the "Measure" for a particular scenario might be "Number of People" and the "Value" would be 100. Okay hopefully my set up makes sense. I need to write a query that will take the sum of integers in the "Value" column for specific "Measures" and make this sum value a new row entry where the "Measure" = "SUM". Now I can do part of that, but the tricky part is that I need to sum those two "Value" integers of the specific "Measure" entries but for each DISTINCT "Scenario_Name" and for every "Version" that might be in the table. The results I need will look something like this. If I have a "Measure" entry for number of people weekdays and number of people weekends for every scenario then I want to generate a new column that will record the total number of people (weekends + weekdays) for each scenario and for each distinct version. I've gotten as far as this lol (I'm still learning a lot of this stuff) SELECT Scenario_Name, Version, SUM(Value) AS Total_People FROM TableName WHERE Measure = 'Weekends' OR Measure = 'Weekdays' GROUP BY Scenario_Name, Version' This is basically what I've worked up to. I can get the sum by using where Scenario_Name="" and Version="" but i don't know how to have this do it for all distinct entries and honestly I have no idea how to this beyond basic stuff.
sql-server-2008sqlsql-server-express
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
I'm a little confused. Can you post some sample data & the output you want?
0 Likes 0 ·
davidt0504 avatar image
davidt0504 answered
I'm going to post all the edits I've made on StackOverflow trying to figure this one out. >Additionally, I have to figure out how to put this idea into a very large (already written but not by me query) that is getting all the stuff that fills this table from multiple databases. EDIT: | Scenario_Name | Measure | Value | |---------------|-------------|-------| | Scenario1 | Weekends | 10 | | Scenario1 | Weekdays | 25 | | Scenario1 | TotalPeople | 35 | | Scenario2 | Weekends | 12 | | Scenario2 | Weekdays | 30 | | Scenario2 | TotalPeople | 42 | | Scenario3 | Weekends | 5 | | Scenario3 | Weekdays | 15 | | Scenario3 | TotalPeople | 20 | There are more entries for "Measure" and the Scenario files will repeat for each version. However, the point is that I want the entry under Measure "TotalPeople" to be the result of a query that pulls the float from Value column and sums both Weekends and Weekdays and puts that value into TotalPeople EDIT: I've continued to figure some stuff out. This is an example of that query I mentioned in my final statment before my first EDIT. SELECT (SELECT attributedata FROM [' +@dbname+ '].table1 WHERE AttributeName = ''Scenario Name'') AS Scenario_Name, (SELECT attributedata FROM [' +@dbname+ '].table1 where AttributeName = ''Version'') AS Version, CAST(COUNT(*) AS float)/MAX(repnum) AS value, finalDisposition AS Measure, GETDATE() AS DateRun, (SELECT ' + CAST(@testid as CHAR) +') AS TestNum FROM [' +@dbname+ '].table2 GROUP BY FinalDisposition I think I simply need to use a UNION to add another one but change what computes as Value and make TotalPeople as Measure So using this, anyone have any ideas on how they would do that? I know that everything except the query that calls Measure and Value need to stay the same. Its also worth noting that the entries for the "Measure" column are being pulled from entries in a different database. Databases that are assosited with each Scenario_Name and pulled using the @dbname variable. So how would I use that format to add in a new "Measure" entry that is called TotalPeople? SELECT CAST(COUNT(id) AS float) AS Value, FinalDisposition AS Measure FROM [' +@dbname '].table2 WHERE FinalDisposition = 'Weekdays' OR FinalDisposition = 'Weekends' GROUP BY FinalDisposition the "id" that is being counted is id for people in the scenario that comes from other databases just like FinalDisposition is a column name from other tables in the databases. This will pull the values that I need but now how can I sum them and have the sum equal a new Measure entry?
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
Not sure about the values of Measures coming from 'another database', but here is a simplified example of making updates based on the logic you stated declare @Simulation table (Scenario_Name varchar(50), Measure varchar(50), Value int) insert into @Simulation select 'Scenario1','Weekends',10 insert into @Simulation select 'Scenario1','Weekdays',25 insert into @Simulation select 'Scenario1','TotalPeople',0 insert into @Simulation select 'Scenario2','Weekends',12 insert into @Simulation select 'Scenario2','Weekdays',30 insert into @Simulation select 'Scenario2','TotalPeople',0 insert into @Simulation select 'Scenario3','Weekends',5 insert into @Simulation select 'Scenario3','Weekdays',15 insert into @Simulation select 'Scenario3','TotalPeople',0 update sim set Value = SummedValues.TotalPeople from @Simulation sim join ( select Scenario_Name, sum( case when Measure = 'Weekends' then Value else 0 end + case when Measure = 'Weekdays' then Value else 0 end ) TotalPeople from @Simulation group by Scenario_Name ) SummedValues on SummedValues.Scenario_Name = sim.Scenario_Name where sim.Measure = 'TotalPeople' select * from @Simulation
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.