Trying to get the sum of distinct values for disti

Question

I'm working in SQL Server Management Studio. 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.

EDIT: I don't want to generate a new column, i want to add in new row entries.

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.

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?

FINAL EDIT (hopefully): This is what I'm working with right now. Well the relevant chunk.

select (cast(count(id) as float)) as Value, 'TotalPeople' as Measure
from table1
where FinalDisposition = 'Weekends' or FinalDisposition = 'Weekdays'
group by FinalDisposition

Everything works except I the sum. All I need now is to be able to sum Value entries together.

Answer 1

This should do the trick:

SELECT 
    Scenario_Name
    , Version
    , Measure
    , Value
 from
 (
    SELECT Scenario_Name, Measure, Version, SUM(Value) AS Value , 1 as ordering
    FROM #TableName
    GROUP BY Scenario_Name, Measure, version
    UNION ALL
    SELECT Scenario_Name, 'TotalPeople', Version, SUM(Value) , 2 as ordering
    FROM #TableName
    GROUP BY Scenario_Name, Version
) t
Order by Scenario_Name, ordering, Measure

---

EDIT: To add on to your query, try something like this (your query goes above where the ... is):

...
UNION ALL
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
    , 'TotalPeople' AS Measure
    , GETDATE() AS DateRun
    , (SELECT ' + CAST(@testid as CHAR) +') AS TestNum 
FROM [' +@dbname+ '].table2 

EDIT2: I think the solution is essentially the same if you are trying to accomplish the same thing:

    select (cast(count(id) as float)) as Value, FinalDisposition as Measure
    from TML_Casualties
    where FinalDisposition = 'DOW' or FinalDisposition = 'KIA'
    group by FinalDisposition
UNION ALL
    select (cast(count(id) as float)) as Value, 'TotalDeaths' as Measure
    from TML_Casualties
    where FinalDisposition = 'DOW' or FinalDisposition = 'KIA'