CODEWITHSUNDEEP
sqlsql-server
user6255180
user6255180

Reputation:

Query to search for a MAX value

I am trying to get the MAX value from my column that is created as percentage and will get from a nvarchar column the information from different things.

This is the query: 

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'
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 
FROM infofile

What I am trying to do is a query that gives me the MAX and MIN percentage from this column. I've tried to use 

SELECT MAX('Percentagem (%)') * 100 
FROM infofile

but it will gives me an error:

Conversion failed when converting the varchar value 'Percentagem (%)' to data type int.

By the way this is a local database which is used on SqlServer

Upvotes: 0

Views: 39

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 95031

Provided your SQL Server version is not too old, you can use window functions:

select 
  filetype as [Extensão], 
  count(*) as [Nº de ficheiros],
  count(*) * 100.0 / sum(count(*)) over () as [Percentagem (%)],
  sum(filesize) as [Total(KB)],
  sum(filesize) / 1024.0 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
order by filetype;

SQL fiddle: http://sqlfiddle.com/#!6/3e0792/1

As to the totals line, it should be possible to generate that elegantly with ROLLUP or GROUP BY GROUPING SETS, but I am not sure how these go along with the window functions. So I cannot give good advice here.

Sometimes it's easiest to get a totals line outside SQL in your app using a programming language.

Here is a working complete query:

select
  [Extensão], 
  sum([Nº de ficheiros]) as [Nº de ficheiros],
  round(sum([Percentagem (%)]), 2) as [Percentagem (%)],
  sum([Total(KB)]) as [Total(KB)],
  round(sum([Convertido para MB]),2) as [Convertido para MB],
  round(min([Min. Percentagem (%)]), 2) as [Min. Percentagem (%)],
  round(max([Max. Percentagem (%)]), 2) as [Max. Percentagem (%)]
from
(
  select 
    filetype as [Extensão], 
    count(*) as [Nº de ficheiros],
    count(*) * 100.0 / sum(count(*)) over () as [Percentagem (%)],
    sum(filesize) as [Total(KB)],
    sum(filesize) / 1024.0 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
) per_filetype
group by rollup([Extensão])
order by case when [Extensão] is null then 2 else 1 end, [Extensão];

SQL fiddle: http://sqlfiddle.com/#!6/3e0792/29

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 95031

Your query fails, because 'Percentagem (%)' is a string starting with a 'P' followed by an 'e' and so on.

For column aliases use the standard SQL delimiter ": MAX("Percentagem (%)"). If you are on SQL Server use the non-standard delimiters [ and ] instead: MAX([Percentagem (%)]).

And the best solution would of course be not to use column or alias names that must be escaped in the first place, e.g. MAX(percentagem).

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

SELECT --MAX('Percentagem (%)') 'Percentagem (%)' will be treated as a string. use [] to enclose the calculated column name
MAX([Percentagem (%)]),MIN([Percentagem (%)]) 
FROM infofile

Upvotes: 1

Related Questions