List:General Discussion« Previous MessageNext Message »
From:Bradley Date:November 13 2001 12:26am
Subject:Sub Count -- Correction
View as plain text  
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


Thread
Sub Count -- CorrectionBradley12 Nov
  • Re: Sub Count -- CorrectionBill Adams12 Nov