Sorry, for some reason, the select statement was cut off. Here is the complete statement (below):
Thanks, Brad 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 HARDWARE_T.IP) -- COUNT NUMBER OF ROUTERS AT EACH LOCATION , COUNT(DISTINCT HARDWARE_T.SLOT) -- COUNT TOTAL # OF SLOTS AT EACH LOCATION , COUNT(DISTINCT HARDWARE_T.PORT) -- COUNT TOTAL # OF PORTS AT EACH LOCATION , COUNT(DISTINCT HARDWARE_T.PORT_STATUS) -- ?? SEPARATE COUNT WHERE HARDWARE_T.PORT_STATUS = 'up' FOR EACH LOCATION ?? , COUNT(DISTINCT HARDWARE_T.PORT_STATUS) -- ?? 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 i.e: 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 --------------------------------------------------------------------- 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
