On Thursday 20 February 2003 02:17 pm, Ajay Patel wrote:
> 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?
Ajay:
You can try GROUP BY SUBSTRING(platformmodel, LOCATE(' ',platformmodel)+1))
A strategically more efficient although more labor-intensive solution would
be to reorganize the data splitting the platformmodel column into smaller
parts to facilitate searches.
--
MySQL Development Team
For technical support contracts, visit https://order.mysql.com/?ref=mspa
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sasha Pachev <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
/_/ /_/\_, /___/\___\_\___/ Provo, Utah, USA
<___/
MySQL Users Conference and Expo
http://www.mysql.com/events/uc2003/
---------------------------------------------------------------------
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
