Hi.
On Mon, Nov 12, 2001 at 01:26:01PM -0800, [EMAIL PROTECTED] wrote:
> Howdy,
>
> I'm trying to write a select statement that produces a SUB COUNT of column
> PORT_STATUS where (A) PORT_STATUS = 'up' and (B) PORT_STATUS = 'down'. I'd
> like to do this in one statment with GROUP by LOCATION_T.ADDRESS,
> LOCATION_T.CITY without altering the outer select. This sort of thing is
> simple to do with PL/SQL. However, this is a mysql database with select
> only. Is it somehow possible to do a sub select into a variable i.e. -->
> SELECT COUNT(DISTINCT COLUMN) INTO VARIABLE WHERE COLUMN = 'up' ?
>
> SELECT LOCATION_T.ADDRESS
> , LOCATION_T.CITY
> , COUNT(DISTINCT
> -- COUNT THE NUMBER OF ROUTERS AT EACH LOCATION
> , COUNT(DISTINCT
> -- COUNT TOTAL # OF SLOTS AT EACH LOCATION
> , COUNT(DISTINCT
> -- COUNT TOTAL # OF PORTS AT EACH LOCATION
> , COUNT(DISTINCT
> -- ?? SEPARATE COUNT WHERE HARDWARE_T.PORT_STATUS =
> 'up' FOR EACH LOCATION ??
> , COUNT(DISTINCT
> -- ?? SEPARATE COUNT WHERE HARDWARE_T.PORT_STATUS =
> 'down' FOR EACH LOCATION ??
If I understand you correctly, you want something like
SUM(HARDWARE_T.PORT_STATUS = 'up'),
SUM(HARDWARE_T.PORT_STATUS = 'down')
This is the short from, the (more) correct form is
SUM( IF(HARDWARE_T.PORT_STATUS = 'up'), 1, 0 ),
SUM( IF(HARDWARE_T.PORT_STATUS = 'down'), 1, 0 )
But since MySQL handles bool values as 1 and 0 one can also use the
first form.
> FROM LOCATION_T, HARDWARE_T
> WHERE LOCATION_T.IP = HARDWARE_T.IP
> GROUP BY LOCATION_T.ADDRESS, LOCATION_T.CITY
[...]
Bye,
Benjamin.
--
[EMAIL PROTECTED]
---------------------------------------------------------------------
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
