SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 help using SUM and COUNT together
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

slowpoke115
Starting Member

2 Posts

Posted - 09/17/2007 :  03:11:49  Show Profile  Reply with Quote
I'm writing a one liner for work and I want the output to display how many computers there are connected to switches where the total number of computers is less than 20 (I've done this already) but the next phase of my script I want to list how many spaces remain (so I want the total -20) and I also want the total numer of free spaces, input and output below:

mysql> SELECT switch, COUNT(*) as "Servers" from dedicated_servers where switch like 'sw%' GROUP by switch HAVING COUNT(switch) < 20 order by Servers;
+-------------+---------+
| switch | Servers |
+-------------+---------+
| switch1-1 | 3 |
| switch1-2 | 6 |
| switch1-3 | 7 |
| switch1-4 | 11 |
| switch1-5 | 12 |
| switch1-6 | 14 |
| switch1-7 | 16 |
| switch1-8 | 16 |
| switch1-9 | 16 |
+-------------+---------+

What I want is an extra column which will display the number of spaces spare, at the moment it just shows how many spaces are being used by computers so what I need is 20 - the count column and I also need as I said before something to add up everything in this new column, I tried sum but struggled :(.

khtan
In (Som, Ni, Yak)

Singapore
17650 Posts

Posted - 09/17/2007 :  03:45:31  Show Profile  Reply with Quote
quote:
mysql> SELECT switch, COUNT(*) as "Servers" from dedicated_servers where switch like 'sw%' GROUP by switch HAVING COUNT(switch) < 20 order by Servers;


Try posting over at a mysql forum. This is a mssql forum site.



KH
Time is always against us

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 09/17/2007 :  04:08:22  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
1 Post at www.mysql.com
2 Try
SELECT switch, COUNT(*) as "Servers",sum(case when condition then 1 else 0 end) as summation from dedicated_servers where switch like 'sw%' GROUP by switch HAVING COUNT(switch) < 20 order by Servers;


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 09/17/2007 :  04:09:00  Show Profile  Reply with Quote
Unless I'm missing something can you not just do:

SELECT switch, 
       COUNT(*) as "Servers",
       20 - COUNT(*) as "Spare slots"
from dedicated_servers 
where switch like 'sw%' 
GROUP by switch 
HAVING COUNT(switch) < 20 
order by Servers

Kristen
Go to Top of Page

slowpoke115
Starting Member

2 Posts

Posted - 09/18/2007 :  02:28:16  Show Profile  Reply with Quote
Kristen that was spot on, thanks so much! I just need a way of doing total spare now, I'm sure I can figure that out on my own though :) Thanks a lot for the help guys

Oh madhivanan your code came up with an error so I didn't use it, thanks anyway though. What a speedy reply!

edit:
I need the sum of the spare slots.

Edited by - slowpoke115 on 09/18/2007 04:36:01
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 09/18/2007 :  02:43:54  Show Profile  Reply with Quote
"I just need a way of doing total spare now"

If I've understood properly its probably this:

SELECT switch, 
       COUNT(*) as "Servers",
       20 - COUNT(*) as "Total Spare slots"
from dedicated_servers 
where switch like 'sw%' 
GROUP by switch 
HAVING COUNT(switch) < 20 
order by Servers

EDIT: OK, I've taken out the mismatched format codes, sorry about that
Kristen

Edited by - Kristen on 09/18/2007 05:57:27
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 09/18/2007 :  04:06:12  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
<<
Oh madhivanan your code came up with an error so I didn't use it,
>>

Well. There must be some syntax error when you tried to implement it

Madhivanan

Failing to plan is Planning to fail

Edited by - madhivanan on 09/18/2007 04:07:00
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 09/18/2007 :  05:07:55  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by slowpoke115

Kristen that was spot on, thanks so much! I just need a way of doing total spare now, I'm sure I can figure that out on my own though :) Thanks a lot for the help guys

Oh madhivanan your code came up with an error so I didn't use it, thanks anyway though. What a speedy reply!

edit:
I need the sum of the spare slots.


Use derived table

SELECT *,sum(Spare_slots) as sum_Spare_slots
from
(SELECT switch, 
       COUNT(*) as Servers,
       20 - COUNT(*) as Spare_slots
from dedicated_servers 
where switch like 'sw%' 
GROUP by switch 
HAVING COUNT(switch) < 20 
) as T


Madhivanan

Failing to plan is Planning to fail

Edited by - madhivanan on 09/18/2007 05:08:37
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 09/18/2007 :  05:09:34  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
<<
edit:
I need the sum of the spare slots.
>>

If you have additional questions, post that as new reply so that we dont need to guess where you edited

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 09/18/2007 :  05:57:58  Show Profile  Reply with Quote
Re-edited my code above which displayed wrongly.

Kristen
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000