2 Replies Latest reply on Apr 19, 2006 11:28 AM by trodzen

    query of query COUNT (DISTINCT ..)

      Hi All, I've got a problem with COUN/T (DISTINCT)

      If I do this in MS SQL IT works:

      SELECT master_col1, master_col2, COUNT(DISTINCT child_col1)
      FROM master
      LEFT OUTER JOIN childs ON (child_col2 = master_col2)
      GROUP BY master_col1, master_col2

      When I do the same using query of queries I get an error:
      0 null

      If I remove the DISTINCT, I don't get an error but I get a count of 1 for any master record that does not have a child record.

      I need the data in the layout to include masters without childs. I'm doing this to improve perforrmance and prefer to not run a separate query or calcs to get the count (of child records per master)

      any ideas?
        • 1. Re: query of query COUNT (DISTINCT ..)
          Dan Bracuk Level 5
          Considering that Q of Q does not support left joins, could you show us the one you wrote that does not get an error?
          • 2. Re: query of query COUNT (DISTINCT ..)
            trodzen Level 1
            i was trying to keep the post simple..

            the left outer join is in the original MS SQL query.. (which caches the data in an application variable.)

            Then on the request, I'm doing a Q of Q to futher sort and select the data. I wrote a workaround that loops through the "grouped" master records and updates the Count field. (with only distinct childs)

            <cfset countqry=getlistqry>
            <!--- this is how i need to present the data --->
            <cfquery name="getlistqry" dbtype="query">
            SELECT Count(my_Key) AS my_Count, SUM(my_Amount) AS my_Amount_Sum, master_Key ...
            FROM getlistqry
            GROUP BY master_Key ...
            ORDER BY #orderby# #direction#, #orderby2#
            <!--- this updates the count field to change the 1 to 0 for master records without a child --->
            <cfloop query="getlistqry">
            <cfquery name="tempqry" dbtype="query">
            SELECT * FROM countqry WHERE child_master_key = #getlistqry.master_Key#
            <cfset temp=QuerySetCell(getlistqry,"my_Count",tempqry.recordcount,getlistqry.currentrow)>

            This updates the count with actual counts of the child records. (ie. it changes the number 1 to a 0 whenever there is NO child.)

            It's not pretty, seems like it could be done in the first Q of Q select. But it gets the job done and probably still a lot faster than trying to do a large MS SQL query at run time.