查询运行max和min

So I am trying to run a query but I'm having some problems with it because I'm using a nvarchar column to get a percentage column which gives me the percentage from the database of different data that I have. That column is called "Filetype" and what I have there is all the Extension's that I put there f.e: .exe, .zip, etc.

所以我试图运行一个查询，但我遇到了一些问题，因为我正在使用一个nvarchar列来获取一个百分比列，它给出了我拥有的不同数据的数据库的百分比。该列被称为“Filetype”，我所拥有的是我放在那里的所有扩展名f.e：.exe，.zip等。

Then I thought I could get in the same query the MAX and MIN of percentage the problem is it is not so easy with that data type values. I've made a query in Microsoft Visual Studio

然后我想我可以在相同的查询中得到MAX和MIN的百分比问题是这个数据类型值不是那么容易。我在Microsoft Visual Studio中进行了查询

SELECT 
Filetype AS [Extensão], 
COUNT(*) AS [Nº de ficheiros],
CAST(((COUNT(Filetype) * 100.0) / (SELECT COUNT(*) FROM infofile)) AS DECIMAL(10,2)) AS [Percentagem (%)],
SUM(Filesize) AS [Total(KB)],
NULL AS [Convertido para MB],
MIN(COUNT(*)) OVER () * 100.0 / (SUM(COUNT(*)) OVER ()) AS [Min. Percentagem (%)],
MAX(COUNT(*)) OVER () * 100.0 / SUM(COUNT(*)) OVER () AS [Max. Percentagem (%)]
FROM infofile  
GROUP BY Filetype
UNION ALL
SELECT '---------------',
COUNT('Nº de extensões'),
((COUNT(Filetype) * 100) / (SELECT COUNT(Filetype) FROM infofile)), 
SUM(Filesize),
SUM(Filesize) / 1024,
NULL,
NULL
FROM infofile

But If I use this query it will fill all the rows and what I want it to do is the same from the lines after UNION ALL. I will show you the output by now

但是，如果我使用此查询，它将填充所有行，我想要它做的是UNION ALL之后的行。我现在会告诉你输出

And I want to display that MAX and MIN as I will show you with arrows.

我想显示MAX和MIN，因为我会用箭头显示。

That row is where I display all the final results. And I want change it there by adding MAX and MIN values

那一行是我显示所有最终结果的地方。我希望通过添加MAX和MIN值来改变它

Your query result

您的查询结果

4 个解决方案

#1

You can do something like this.

你可以做这样的事情。

with cte as
(
SELECT 
Filetype AS [Extensão], 
COUNT(*) AS [Nº de ficheiros],
CAST(((COUNT(Filetype) * 100.0) / (SELECT COUNT(*) FROM infofile)) AS DECIMAL(10,2)) AS [Percentagem (%)],
SUM(Filesize) AS [Total(KB)],
NULL AS [Convertido para MB],
MIN(COUNT(*)) OVER () * 100.0 / (SUM(COUNT(*)) OVER ()) AS [Min. Percentagem (%)],
MAX(COUNT(*)) OVER () * 100.0 / SUM(COUNT(*)) OVER () AS [Max. Percentagem (%)]
FROM infofile  
GROUP BY Filetype
)
select [Extensão],[Nº de ficheiros],[Percentagem (%)],[Total(KB)],[Convertido para MB],NULL AS [Min. Percentagem (%)],NULL AS [Max. Percentagem (%)]   from cte
UNION ALL
SELECT '---------------',
COUNT('Nº de extensões'),
((COUNT(Filetype) * 100) / (SELECT COUNT(Filetype) FROM infofile)), 
SUM(Filesize),
SUM(Filesize) / 1024,
(Select MAX([Min. Percentagem (%)]) from cte) as [Min. Percentagem (%)] ,
(Select MAX([Max. Percentagem (%)] from cte as [Max. Percentagem (%)])
FROM infofile

I have done nothing but put your 1st query in a cte and used it to return your min amd max % for the query after UNION ALL as well. I hope this is your expected output.

我没有做任何事情，只是把你的第一个查询放在一个cte中，然后用它来返回你在UNION ALL之后查询的最小最大值％。我希望这是你的预期输出。

#2

You could replicate your logic in a cross join to the data in the second query, something like this;

您可以将交叉连接中的逻辑复制到第二个查询中的数据，如下所示;

SELECT Filetype AS [Extensão]
    ,COUNT(*) AS [Nº de ficheiros]
    ,CAST((
            (COUNT(Filetype) * 100.0) / (
                SELECT COUNT(*)
                FROM infofile
                )
            ) AS DECIMAL(10, 2)) AS [Percentagem (%)]
    ,SUM(Filesize) AS [Total(KB)]
    ,NULL AS [Convertido para MB]
    ,MIN(COUNT(*)) OVER () * 100.0 / (SUM(COUNT(*)) OVER ()) AS [Min. Percentagem (%)]
    ,MAX(COUNT(*)) OVER () * 100.0 / SUM(COUNT(*)) OVER () AS [Max. Percentagem (%)]
FROM infofile
GROUP BY Filetype

UNION ALL
(
SELECT '---------------'
    ,COUNT('Nº de extensões')
    ,(
        (COUNT(Filetype) * 100) / (
            SELECT COUNT(Filetype)
            FROM infofile
            )
        )
    ,SUM(Filesize)
    ,SUM(Filesize) / 1024
    ,a.[Min. Percentagem (%)]
    ,a.[Max. Percentagem (%)]
FROM infofile i
CROSS JOIN
(SELECT 
MAX(a.[Min. Percentagem (%)]) [Min. Percentagem (%)]
,MAX(a.[Max. Percentagem (%)]) [Max. Percentagem (%)]
FROM
(SELECT Filetype AS [Extensão]
    ,COUNT(*) AS [Nº de ficheiros]
    ,CAST((
            (COUNT(Filetype) * 100.0) / (
                SELECT COUNT(*)
                FROM infofile
                )
            ) AS DECIMAL(10, 2)) AS [Percentagem (%)]
    ,SUM(Filesize) AS [Total(KB)]
    ,NULL AS [Convertido para MB]
    ,MIN(COUNT(*)) OVER () * 100.0 / (SUM(COUNT(*)) OVER ()) AS [Min. Percentagem (%)]
    ,MAX(COUNT(*)) OVER () * 100.0 / SUM(COUNT(*)) OVER () AS [Max. Percentagem (%)]
FROM infofile
GROUP BY Filetype) ft
) a
)

#3

If I get your question correctly, you need to add an aggregated row to your select statement, where you can derive the min and max of the aggregated rows. Here is what you can do using a #temp table. Further you can drop the #temp table once not needed.

如果我正确地得到了您的问题，您需要在select语句中添加一个聚合行，您可以在其中派生聚合行的最小值和最大值。以下是使用#temp表可以执行的操作。此外，您可以在不需要时删除#temp表。

SELECT 
Filetype AS [Extensão], 
COUNT(*) AS [Nº de ficheiros],
CAST(((COUNT(Filetype) * 100.0) / (SELECT COUNT(*) FROM infofile)) AS DECIMAL(10,2)) AS [Percentagem (%)],
SUM(Filesize) AS [Total(KB)],
SUM(Filesize) / 1024 AS [Convertido para MB],
CAST(((COUNT(Filetype) * 100.0) / (SELECT COUNT(*) FROM infofile)) AS DECIMAL(10,2)) AS [Min. Percentagem (%)],
CAST(((COUNT(Filetype) * 100.0) / (SELECT COUNT(*) FROM infofile)) AS DECIMAL(10,2)) AS [Max. Percentagem (%)]
INTO #temp
FROM infofile  
GROUP BY Filetype;

SELECT *
FROM #temp
UNION ALL
SELECT '---------------',
SUM([Nº de ficheiros]),
SUM([Percentagem (%)]), 
SUM([Total(KB)]),
SUM([Total(KB)]) / 1024,
MIN([Min. Percentagem (%)]),
MAX([Max. Percentagem (%)])
FROM #temp;

DROP TABLE #temp;

Let me know if this doesn't work out.

如果这不成功，请告诉我。

#4

Probably no need for min/max percentage at first part, just at the second. As far as I've got it from comments, look at

可能不需要第一部分的最小/最大百分比，仅在第二部分。至于我从评论中得到它，看看

WITH totals_by_ext AS (
  SELECT 
    Filetype AS [Extensão], 
    COUNT(*) AS [Nº de ficheiros],
    CAST(((COUNT(Filetype) * 100.0) / (SELECT COUNT(*) FROM infofile)) AS DECIMAL(10,2)) AS [Percentagem (%)],
    SUM(Filesize) AS [Total(KB)],
    CAST(NULL AS DECIMAL(10,2)) AS [Convertido para MB],
    CAST(NULL AS DECIMAL(10,2)) AS [Min. Percentagem (%)],
    CAST(NULL AS DECIMAL(10,2)) AS [Max. Percentagem (%)]
  FROM infofile  
  GROUP BY Filetype
)
SELECT * 
FROM totals_by_ext
UNION ALL
SELECT 
  '---------------',
  COUNT([Extensão]),
  SUM([Nº de extensões]) * 100 / (SELECT COUNT(Filetype) FROM infofile)),
  SUM([Total(KB)]),
  SUM([Total(KB)]) / 1024,
  MIN([Percentagem (%)]) AS [Min. Percentagem (%)],
  MAX([Percentagem (%)]) AS [Max. Percentagem (%)]
FROM totals_by_ext;

#1