Thanks in advance for anyone who can lend a hand with a few minor issues I'm having.
I'd like to first clarify that I knew NOTHING about SQL queries before a few days ago, and I've been teaching myself a crash course by using THWACK and a handful of useful SQL tutorials and training sites, so I may be making this more difficult than it needs to be. I have over 100 wireless mobility controllers deployed at different sites throughout the country, each with a number of wireless access points (with a total of about 5600 APs being monitored). I'm trying to create a custom web report using a SQL query of the database of wireless controllers. The point of the query is to return the current availability percentage of APs per controller. Here's the query I have written, and a breakdown of my mentality. Note, this query works and returns what I am looking for; however, I can not get the SORT BY or ORDER BY clauses to work on the main SELECT statement:
SELECT ap.controller_name, CAST(ROUND((SELECT 100*(COUNT(CASE WHEN ap2.status=1 THEN 1 ELSE NULL END)*1.0)/(count(*)) FROM NPM_NV_WL_APS AP2 WHERE ap2.controller=ap.controller_name), 2, 0) AS DECIMAL(18,2)) AS percentage
FROM NPM_NV_WL_CONTROLLERS AP
I'm basically pulling two values for a table from the NPM controller database. One is the controller name, and the other is a decimal value that is calculated using a sub-query of the NPM AP database. The sub-query returns a value of the count of 'UP' APs divided by the total count of APs from the AP database. Since this will result in the percentage of total 'UP' APs across the portfolio (again, about 5600), the WHERE clause limits the sub-query to return the APs whose "controller" value from the AP database matches the "controller_name" value from the controller database.
After hours of trial and error and to my surprise, the above query works. It will return a controller name and the percentage of APs with the status of "UP". My problem comes when trying to use the SORT BY clause after "FROM NPM_NV_WL_CONTROLLERS AP". My understanding is that I should be able to sort the columns of either "controller_name" (which comes directly from the database) or the value of "percentage", that gets defined by the SELECT sub-query of APs. This gives me a "Query is not valid" response. I have a feeling this has to do with the fact that one value is the result of a sub-query, but my new knowledge of SQL queries is not as expansive as I'd like it to be. Does anyone have any ideas how to work around this?
