help using SUM and COUNT together

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

SQL Server 2000 Forums

SQL Server Development (2000)

help using SUM and COUNT together

Author

Topic

slowpoke115
Starting Member

2 Posts

Posted - 2007-09-17 : 03:11:49

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)

17689 Posts

Posted - 2007-09-17 : 03:45:31

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
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-17 : 04:08:22

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

Kristen
Test

22859 Posts

Posted - 2007-09-17 : 04:09:00

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

slowpoke115
Starting Member

2 Posts

Posted - 2007-09-18 : 02:28:16

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.

Kristen
Test

22859 Posts

Posted - 2007-09-18 : 02:43:54

"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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-18 : 04:06:12

<<
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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-18 : 05:07:55

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-18 : 05:09:34

<<
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

Kristen
Test

22859 Posts

Posted - 2007-09-18 : 05:57:58

Re-edited my code above which displayed wrongly.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources