In the select statement, MySQL returns 1 for true so:
SELECT LOCATION_T.ADDRESS, LOCATION_T.CITY, COUNT(DISTINCT HARDWARE_T.IP), COUNT(DISTINCT HARDWARE_T.SLOT), COUNT(DISTINCT HARDWARE_T.PORT), ----- SUM( HARDWARE_T.PORT_STATUS = 'up' ) AS up_cnt, SUM( HARDWARE_T.PORT_STATUS = 'down' ) as dn_cnt, ---- FROM LOCATION_T, HARDWARE_T WHERE LOCATION_T.IP = HARDWARE_T.IP GROUP BY LOCATION_T.ADDRESS, LOCATION_T.CITY b. Bradley wrote: > 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 -- Bill Adams TriQuint Semiconductor --------------------------------------------------------------------- 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
