Sort and Group - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sort and Group

Author

Topic

superleeds
Starting Member

9 Posts

Posted - 2008-01-11 : 08:25:24

Hi All,

Running SQL 2005

DB as a colum that contains hundreds of file types ie, mp3,wav,txt,doc,pdf, etc

When i run the folowing

use DB

go

select file1

from dbo.DB

where file1 like '%'

I get back (4192103 row(s) affected)

What would run to get back how many mp3's,docs'txt files there are?

Kind Regards

Leeds

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-01-11 : 08:29:34

Do you have file type as a column in the table?

Select FileType, count(*)
from Table
group by FileType

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-11 : 09:15:27

Also note that where file1 like '%' would return all rows. If you want to find the file that has %, then use where file1 like '[%]'

Madhivanan

Failing to plan is Planning to fail

superleeds
Starting Member

9 Posts

Posted - 2008-01-14 : 06:35:48

Thanks for that, but is there a way I can get an amount of each file
say like

mp3 2345
doc 345
txt 456456
ini 34

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-14 : 06:45:08

~~Harsh told you how, three days ago.~~

-- Prepare sample data
DECLARE	@Sample TABLE (FileName VARCHAR(257))

INSERT	@Sample
SELECT	'c:\peso\ago.gif' UNION ALL
SELECT	'd:\windows\system\start.gif' UNION ALL
SELECT	'http://www.developerworkshop.net/babyDBA.jpg' UNION ALL
SELECT	'test'

-- Show the expected output
SELECT		Extension,
		COUNT(*) AS Items
FROM		(
			SELECT	CASE
					WHEN FileName LIKE '%.%' THEN RIGHT(FileName, CHARINDEX('.', REVERSE(FileName)) - 1)
					ELSE 'No filetype/extension'
				END AS Extension
			FROM	@Sample
		) AS d
GROUP BY	Extension
ORDER BY	Extension

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-14 : 06:46:50

quote:
Originally posted by superleeds

Thanks for that, but is there a way I can get an amount of each file
say like

mp3 2345
doc 345
txt 456456
ini 34

by amount, did you mean count or file size?

Madhivanan

Failing to plan is Planning to fail

superleeds
Starting Member

9 Posts

Posted - 2008-01-14 : 07:49:31

It was the actual Count i was looking for,

I have tried all the above but it doesn't work :(

Cheers

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-14 : 08:00:19

Not even the one suggested to you 01/14/2008 : 06:45:08 ?

E 12°55'05.25"
N 56°04'39.16"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-01-14 : 08:09:50

Then probably you should give us Table structures, sample data with your question. Incomplete information is going to end up giving incomplete/wrong solutions.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-14 : 08:10:13

quote:
Originally posted by superleeds

It was the actual Count i was looking for,

I have tried all the above but it doesn't work :(

Cheers

What do you mean by "it doesn't work"?
Did you get any error?

Madhivanan

Failing to plan is Planning to fail

superleeds
Starting Member

9 Posts

Posted - 2008-01-14 : 08:16:04

Afraid not :(

the one that harsh suggested did work, but it gives me back the amount of files on each PC in the organization, over 3000 machines, I just want an overall count of each of the files in the org.

Thanks for the help on this, very much appreciated indeed

superleeds
Starting Member

9 Posts

Posted - 2008-01-16 : 07:06:03

Hope this helps a bit more with my issue

Trying to get this result

Hostname username file1 discovereddevice displayname

the tables are

Table 1

UniqueID hostname username
382119a3-215e-6 orbiter223 mellyt

Table2

File1 time discovereddevice
Mp3 09/11/2007 16:40:54 HP DATASTORE
Doc
txt

table3

deviceclass deviceflags displayname
4df45rse456fg 20361645 HP

superleeds
Starting Member

9 Posts

Posted - 2008-01-17 : 09:03:13

Any Ideas please?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-17 : 09:29:09

SELECT File1, COUNT(*) AS Files
FROM Table2 GROUP BY File1
ORDER BY COUNT(*) DESC

E 12°55'05.25"
N 56°04'39.16"

superleeds
Starting Member

9 Posts

Posted - 2008-01-17 : 09:49:12

Thank you Peso.worked a treat.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-17 : 10:06:39

You are welcome.

But Harsh told you exactly the same thing 01/11/2008 : 08:29:34 !

E 12°55'05.25"
N 56°04'39.16"

superleeds
Starting Member

9 Posts

Posted - 2008-01-17 : 10:26:17

Sorry Harsh, i must have done a typo that day, so thank you kindly, the both of you that is.

would i be asking to much if you ciould tell me the following

Trying to get this result

Hostname username file1 discovereddevice displayname

the tables are

Table 1

UniqueID hostname username
382119a3-215e-6 orbiter223 mellyt

Table2

File1 time discovereddevice
Mp3 09/11/2007 16:40:54 HP DATASTORE
Doc
txt

table3

deviceclass deviceflags displayname
4df45rse456fg 20361645 HP

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-17 : 10:29:28

Use INNER JOIN in your query.

E 12°55'05.25"
N 56°04'39.16"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-01-17 : 10:29:37

What is the common column in these three tables? how they are related?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-17 : 10:30:44