Counts needed for data in random sequence

Forum|Forum|16 years ago
August 3, 2009
4 replies
2389 views

I have a table that is grouped by entry date, agency, last name. I need to know how many records within entry date & agency have a disposition of "Dep', how many have a disposition of "Can" etc,

Thank you for any help you can give.

Reporting

This topic has been closed for replies.

C

Cozmo2Author

Inspiring

My query looks like this:

<cfquery name="CFReportDataQuery" datasource="#session.ds#">
SELECT
name."last_name", name."first_name", name."mi", name."div_cod", name."dept_cod",
name."camp_cod", name."birthdate", name."ethnic_cod", name."gender", address."preferred",
address."e_mail", address."st_addr", address."add_addr", address."city", address."state",
address."zip", address."phone", prospect."soc_sec", prospect."date_inq", prospect."app_rec",
prospect."date_rec", prospect."term_int", prospect."intrlevcod", prospect."enstat_cod",
prospect."prospect_ce1", approg."soc_sec", approg."prg_cod", approg."entry_date", approg."app_date",
approg."major_ap", dispos."soc_sec", dispos."CurDispos", dispos."dispos", sysvar."title",
inqtype."inqtypetxt", rpt_disp."dispos", rpt_dts."from_dt", rpt_dts."to_dt", agency."agency_cod",
agency."agency_txt"
FROM
    { oj ((((((((((((((("name" name INNER JOIN "campus" campus ON
        name."camp_cod" = campus."camp_cod")
INNER JOIN "address" address ON
        name."soc_sec" = address."soc_sec")
INNER JOIN "approg" approg ON
        name."soc_sec" = approg."soc_sec")
     LEFT OUTER JOIN "dispos" dispos ON
        approg."soc_sec" = dispos."soc_sec" AND
        approg."app_rid" = dispos."app_rid")
INNER JOIN "rpt_div" rpt_div ON
        name."div_cod" = rpt_div."div_cod")
     INNER JOIN "nmcat" nmcat ON
        name."soc_sec" = nmcat."soc_sec")
     INNER JOIN "prospect" prospect ON
        name."soc_sec" = prospect."soc_sec")
     INNER JOIN "inqtype" inqtype ON
        prospect."inqtypecod" = inqtype."inqtypecod")
     INNER JOIN "rpt_dept" rpt_dept ON
        name."dept_cod" = rpt_dept."dept_cod")
     INNER JOIN "rpt_camp" rpt_camp ON
        campus."camp_cod" = rpt_camp."camp_cod")
     INNER JOIN "rpt_agency" rpt_agency ON
        prospect."agency_cod" = rpt_agency."agency_cod")
     LEFT OUTER JOIN "rpt_disp" rpt_disp ON
        dispos."dispos" = rpt_disp."dispos")
     INNER JOIN "rpt_appentrydt" rpt_appentrydt ON
        approg."entry_date" = rpt_appentrydt."entry_date")
     INNER JOIN "agency" agency ON
        rpt_agency."agency_cod" = agency."agency_cod")
     INNER JOIN "rpt_dts" rpt_dts ON
        rpt_camp."token" = rpt_dts."token")
INNER JOIN "sysvar" sysvar ON
        prospect."term_int" <> sysvar."title"}
WHERE
(dispos."CurDispos" = '1' OR dispos."CurDispos" is null) AND
address."preferred" = '1' AND
rpt_camp."token" = '#session.token#' AND
    rpt_appentrydt."token" = '#session.token#' AND
    rpt_div."token" = '#session.token#' AND
    rpt_agency."token" = '#session.token#' AND
    rpt_dts."token" = '#session.token#' AND
    rpt_dept."token" = '#session.token#' AND
    (rpt_disp."token"='#session.token#' OR rpt_disp."token" is null)
ORDER BY
approg."entry_date" ASC,
    rpt_agency."agency_cod" ASC,
    name."last_name" ASC,
    name."first_name" ASC,
    dispos."dispos" ASC
</cfquery>

I need counts for the different dispos.dispos (there are 9 of them).

Thanks

BKBK

Community Expert

Something like this?

SELECT COUNT(*) AS disposCount, dispos
from
(
/*Your select query*/
)
GROUP BY dispos

C

Cozmo2Author

Inspiring

I'm not sure what you mean by Query of a query. I was able to use calculated field. Would a query of a query be more efficient? If so, how would I go about adding that to my report?

Thanks for your response.