query help

[Ajay Patel]

I have a host table where I save the platform model. The platform model
can be saved in various ways. For example,

Sun Enterprise 250 (2 x UltraSPARC 164MHz)
Sun Enterprise 250 (2 x UltraSPARC-II 400MHz)
Sun Enterprise 250 (2 x UltraSPARC-II 450MHz)
Sun 250

etc. I can get the count by:

select platformmodel, count(*) from host
where platformmodel like '%250'
group by platformmodel;

This gives me the counts for each of the above models.
Sun Enterprise 250 (2 x UltraSPARC 164MHz) 4
Sun Enterprise 250 (2 x UltraSPARC-II 400MHz) 100
Sun Enterprise 250 (2 x UltraSPARC-II 450MHz) 3
Sun 250 1000

Since they are the same model I would rather have a single line output giving
me the sum of all of the above counts. That is,

Sun E250 1107

Is there a quick and easy way to do this?

To complicate it a little bit, how about getting the sum for all different
platform types. The following query:

select platformmodel, count(*) from host
group by platformmodel;

gives me this result:

+----------------------------------------------------+----------+
| platformmodel | count(*) |
+----------------------------------------------------+----------+
| 16-slot Sun Enterprise 6000 | 174 |
| 16-slot Sun Enterprise E6500 | 728 |
| 16-slot Ultra Enterprise 6000 | 4 |
| 4-slot Sun Enterprise 3000 | 310 |
| 4-slot Ultra Enterprise 3000 | 17 |
| 420R (4 X UltraSPARC-II 450MHz) | 2 |
| 5-slot Sun Enterprise E3500 | 657 |
| 8-slot Sun Enterprise 4000/5000 | 659 |
| 8-slot Sun Enterprise E4500/E5500 | 1939 |
| 8-slot Ultra Enterprise 4000/5000 | 19 |

and so on. As you can see, due to the platform type being entered
differently, I cannot group them correctly. The preferred output
would be:

+----------------------------------------------------+----------+
| platformmodel | count(*) |
+----------------------------------------------------+----------+
| Sun Enterprise 6000 | 178 |
| 16-slot Sun Enterprise E6500 | 728 |
| Sun Enterprise 3000 | 317 |

and so.

Can this be done in SQL or would I have to script it?

TIA,
Ajay

Ajay S. Patel
email: [EMAIL PROTECTED]
Phone: 937-291-3026
Address : 8889 Paragon Rd.
Centerville, OH 45458



---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php