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 ??
FROM LOCATION_T, HARDWARE_T
WHERE LOCATION_T.IP = HARDWARE_T.IP
GROUP BY LOCATION_T.ADDRESS, LOCATION_T.CITY
Output to look like:
ADDRESS CITY Number of Routers
Number of Slots Number of Ports Number of Ports UP Number of
Ports DOWN
________ ___ _______________
____________ ____________ ________________
___________________
32 Street New York 8
90 300 150
150
52 Street New York 12
120 400 200
200
Thanks in advance..
Brad
---------------------------------------------------------------------
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
