尝试获取不同软件版本的不同文件的不同值的总和 [英] Trying to get the sum of distinct values for distinct files for distinct software versions

问题描述

我在SQL Server Management Studio中工作。我有一个表中有模拟数据。其中四列是Version，Scenario_Name，Measure和Value。 版本列将具有软件版本号。 Scenario_Name列将包含其中的方案的文件名。 度量列将基本上具有类似时间，金钱，成本等场景的数据类型条目。值列将记录与度量列相关联的数字。例如，特定场景的度量可能是人数，值将是100.好的，我的设置是有意义的。

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.

我需要编写一个查询，它将获取特定度量的值列中的整数之和，并使此和值成为新的行条目，其中度量=SUM。现在我可以做一部分，但棘手的部分是，我需要总结特定的测量条目的两个值整数，但对于每个DISTINCTScenario_Name和每个版本可能在表中。

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 don't want to generate a new column, i want to add in new row entries.

我已经到这个lol了（我还在学习很多这个东西）

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

这基本上是我所做的。我可以通过使用Scenario_Name =和版本=，但我不知道如何做到这一点，所有不同的条目，我真的不知道如何超越基本的东西。

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.

编辑：

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

度量和场景文件的条目将为每个版本重复。然而，关键是我想要测量TotalPeople下的条目是一个查询的结果，它从Value列中提取浮动，并将周末和周日汇总，并将该值存入TotalPeople

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

编辑：我继续想出一些东西。这是我在我的第一次编辑之前的最终版本中提到的查询的示例。

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

我想我只需要使用一个UNION另一个，但改变什么计算为值，并使总人作为测量

I think I simply need to use a UNION to add another one but change what computes as Value and make TotalPeople as Measure

所以使用这个，任何人都有任何想法，他们怎么做？我知道除了调用Measure和Value的查询之外的所有内容都需要保持不变。

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.

它还值得注意的是，Measure列的条目是从条目在不同的数据库。与每个Scenario_Name配合使用并使用@dbname变量拉取的数据库。那么如何使用该格式添加一个名为TotalPeople的新Measure条目？

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

正在计数的id是来自其他数据库的场景中的人员的ID，就像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（希望）：
这是我现在正在使用的。

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.

推荐答案

这应该可以做到：

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

---

编辑：
对您的查询，尝试这样（您的查询高于...的地方）：

---

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'

这篇关于尝试获取不同软件版本的不同文件的不同值的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！