Rekcs Rekcs - 6 months ago 10
SQL Question

Query to run max and 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.

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

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 nowenter image description here

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

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

Your query result
enter image description here

Answer

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.

Comments