Help with SQL Query - 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

Help with SQL Query

Author

Topic

dimepop
Starting Member

33 Posts

Posted - 2012-07-06 : 09:26:33

Hi i have a query that returns this result..
QUERY:
SELECT Computername, TimeToLogon FROM dbo.boottime
ComputerName TimeToLogon
Desktop-152 129
Desktop-038 120
Desktop-366 84
Desktop-054 132
Desktop-053 150
Desktop-433 0
Laptop-043 189
Laptop-017 69
Laptop-357 165
Laptop-133 170
Laptop-153 60

It has of course many more records that these..
I would like to get the percentage of Desktops which TimetoLogon is Over 60

How can i achieve this?
Thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-06 : 09:29:56

select
100.0 * (select count(*) from boottime where ComputerName like 'Desktop%' and TimeToLogon > 60)
/
(select count(*) from boottime where ComputerName like 'Desktop%')

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-06 : 09:32:08

[code]
SELECT LEFT(ComputerName,CHARINDEX('-',ComputerName)-1),
COUNT(CASE WHEN TimeToLogon > 60 THEN 1 END) * 100.0/COUNT(1) AS [%Over60]
FROM dbo.boottime
WHERE ComputerName LIKE 'Desktop%'
GROUP BY LEFT(ComputerName,CHARINDEX('-',ComputerName)-1)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dimepop
Starting Member

33 Posts

Posted - 2012-07-06 : 09:49:32

Hi guys good effort but it doesn't seem to work.
Both queries execute successfully but they don't return any values.. ??
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-06 : 09:51:56

quote:
Originally posted by dimepop

Hi guys good effort but it doesn't seem to work.
Both queries execute successfully but they don't return any values.. ??
Thanks

Then I'm sure you've some other part which you've not told us so far. Are you sure the values exist exactly like what you've shown in previos post?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dimepop
Starting Member

33 Posts

Posted - 2012-07-06 : 09:52:35

Hi, actually this query returned with errors:

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

quote:
Originally posted by nigelrivett

select
100.0 * (select count(*) from boottime where ComputerName like 'Desktop%' and TimeToLogon > 60)
/
(select count(*) from boottime where ComputerName like 'Desktop%')

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-07-06 : 09:53:18

Provide a little more info. Follow the link in my sig, it will tell you how to provide said info.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-06 : 10:00:34

[code]
SELECT LEFT(ComputerName,CHARINDEX('-',ComputerName)-1),
COUNT(CASE WHEN TimeToLogon > 60 THEN 1 END) * 100.0/NULLIF(COUNT(1),0) AS [%Over60]
FROM dbo.boottime
WHERE ComputerName LIKE 'Desktop%'
GROUP BY LEFT(ComputerName,CHARINDEX('-',ComputerName)-1)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dimepop
Starting Member

33 Posts

Posted - 2012-07-06 : 10:11:31

Hi, i am now running them and they seem to work, with both queries showing the same results.
Before visakh16 was showing a different result.
Thanks a lot for the help, this community is great.
Can you guys suggest some tutorials to learn how to make my own queries?
There is some many stuff around the web that i just get lost.
Many Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-06 : 10:33:52

quote:
Originally posted by dimepop

Hi, i am now running them and they seem to work, with both queries showing the same results.
Before visakh16 was showing a different result.
Thanks a lot for the help, this community is great.
Can you guys suggest some tutorials to learn how to make my own queries?
There is some many stuff around the web that i just get lost.
Many Thanks

start with below to learn basics

http://www.w3schools.com/sql/default.asp

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources