Remove double entries from Query 1

[NavMen]

Hi,

I try to get only one row from a table, when I run “select PhysicalFile, Size_KB from SQLDBSIZE” I get all the physical file locations:

C:\Program Files\Microsoft SQL Server\MSSQL. \MSSQL\DATA\WSS_Content_Partners.mdf
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\WSS_Content_Partners.mdf
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\WSS_Content_Partners.mdf
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\WSS_Content_Partners.mdf
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\WSS_Content_STEALTH.mdf

But sometimes this location will pop up multiple times. I need then only once this entry. I don’t know how to do this.

Thanks,

Navmen

 

[NavMen]

I found the answer:

select distinct(PhysicalFile), Size_KB from STH_SQLDBSIZE

solves my problem.

Thanks

 

[IanWaterman]

just add distinct to query

select distinct .....


Ian

 

[NavMen]

Hi,

Still a small problem, how can I get the SUM of the Size_KB

select distinct(PhysicalFile), SUM(Size_KB/1048576) from STH_SQLDBSIZE

This still gives me the SUM of all the files, I need only from unique entries?

Thanks,

Navmen

 

[IanWaterman]

If its the same value change quey to

select PhysicalFile, maximum(Size_KB/1048576) from STH_SQLDBSIZE
group by PhysicalFile

Ian

 

[IanWaterman]

Sorry use max not maximum

Ian

 

[NavMen]

Ian,

Thanks,this gives the size of only one file.

The query: select distinct(PhysicalFile), Size_KB/1048576 from STH_SQLDBSIZE

Gives:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\WSS_Content_Partners.mdf 0.08807373
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\WSS_Content_STEALTH.mdf 0.04705810

Now I need only the total of all files?

Any solution for this
Thanks,
Navmen

 

[IanWaterman]

I think this should work

select PhysicalFile, sum(Size_KB/1048576) as size
from(
select distinct PhysicalFile, Size_KB/1048576 from STH_SQLDBSIZE)
group by PhysicalFile

Ian

 

[NavMen]

Ian,

When I run this query I got the message :

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'group'.\

When I remove Group By I got the message:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ')'.

Thanks,

Navmen

 

[IanWaterman]

Sorry I work in both Oracle and MS SQL and I forgot MS SQL insists on aliases for subqueries

select q.PhysicalFile, sum(q.Size_KB/1048576) as size
from(
select distinct PhysicalFile, Size_KB/1048576 from STH_SQLDBSIZE) q
group by q.PhysicalFile

Ian

 

[NavMen]

Some message on this query:

Msg 8155, Level 16, State 2, Line 7
No column was specified for column 2 of 'q'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Size_KB'.

is a select after From supportd?

Thanks,

Navmen

 

[IanWaterman]

Oops brackets and syntax in wrong place when dividing by 1048576

select q.PhysicalFile, sum(q.Size_MB)as Size_MB
from(
select distinct PhysicalFile, Size_KB/1048576 as Size_MB from STH_SQLDBSIZE) q
group by q.PhysicalFile

Ian

 

[NavMen]

Hi Ian,

GREAT Job, I modified it to:

select sum(q.Size_MB)as Size_MB from (select distinct PhysicalFile, Size_KB/1048576 as Size_MB from STH_SQLDBSIZE) q

And got the toital size of all files.

Thanks,

Navmen