Complex Query Question

Question

I am doing a 9-1-1 reporting system. I am selecting calls by Agency and type of call for a given month. That works fine, but I need them printed in desending sequence by number call far a call type. Don't see anyway to accomplish that with one query.
Is that a way? If not, the second part of this question is that I have not created a table using SQL. I need 3 fields, Field_A is Text (9 positions); Field_B is Text (40 Positions) and the last is my summarized count from the query. How do I create a Table to support this function?

Query
<cfquery name="GetEventCount" datasource="JasperCountyDB">
select count(AG_ID) as EventCount, TYCOD, TYP_ENG, CURENT
from aeven
where ((AG_ID = '#Session.InquiryAgencyCode#')
AND (CURENT = 'T') AND (CDTS LIKE '#EventReportDate#'))
group by AG_ID, TYCOD, TYP_ENG, CURENT
</cfquery>

Thanks for any guideance that you can provide.
Ron

Anonymous · Accepted Answer

PhilThanks, that solved my issue. Is there a good SQL book thatgets into the more complex queries and table joins, etc.?Thanks AgainRon

paross1 · Answer

Your question wasn't very clear, but if you only want to sort them by the count in descending order, just ORDER BY 1 DESC.

<cfquery name="GetEventCount" datasource="JasperCountyDB">
SELECT COUNT(AG_ID) as EventCount, TYCOD, TYP_ENG, CURENT
FROM aeven
WHERE AG_ID = '#Session.InquiryAgencyCode#'
AND CURENT = 'T'
AND CDTS LIKE '#EventReportDate#'
GROUP BY AG_ID, TYCOD, TYP_ENG, CURENT
ORDER BY 1 DESC
</cfquery>

Phil

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.