Skip navigation
Max_Donnelly
Max_Donnelly
Currently Being Moderated

Problem with Group By on Multiple Columns

Jun 8, 2006 5:30 PM

I have a table with 5 columns:
id | provider | referrer | pt_count | address1

I'm trying to create a report page which groups by address1, creates a sum total of the pt_count for all address1 records, then displays a list of all referrers, then a list of all providers associated with address1.

I am able to group the address1 results and get the sum total of the pt_count for each address1 with the following SELECT statement:

<CFQUERY name="tuccreport" datasource="#myDB#" dbtype="ODBC">
SELECT DISTINCT address1, SUM(pt_count) AS Total_Patients, referrer, provider
FROM tucc_final
GROUP BY address1
ORDER BY Total_Patients desc
</cfquery>

When I output the results:
<CFSET myCounter="1">
<CFOUTPUT query="tuccreport" GROUP="address1">
<tr>
<td id="data0" valign="top">#myCounter#</td>
<td id="data1" valign="top">#address1#</td>
<td id="data2" valign="top">#Total_Patients#</td>
<td id="data3" valign="top"></td>
<td id="data4" valign="top"></td>
</tr>
<CFSET myCounter=(#myCounter#+1)>
</CFOUTPUT>

I get a table with a counter, then the first two fields just as I want them: An address, then a sum total of the pt_count field for all instances of that address.

Now all I need to do is add the groups of referrer and providers, and I'm done. But as soon as I change the query to:

<CFQUERY name="tuccreport" datasource="#myDB#" dbtype="ODBC">
SELECT DISTINCT address1, SUM(pt_count) AS Total_Patients, referrer, provider
FROM tucc_final
GROUP BY address1, referrer
ORDER BY Total_Patients desc
</cfquery>

The results start getting broken down. My address group is still getting some sort of partial total, but it is broken down so it appears spread out over multiple rows in the report table.

Same thing happens if I add "provider" to the Group By, it breaks the results down even further. There is still grouping going on based on Address1, but the Address1 group appears to be getting broken into chunks somehow.

Any ideas?

Thanks,
max
561 Views   12 Replies   Latest reply: philh, Jun 12, 2006 9:27 AM
 
Replies
  • Dan Bracuk
    Currently Being Moderated
    1. Dan Bracuk,
    Jun 8, 2006 8:12 PM   in reply to Max_Donnelly
    Report
    First you don't need the distinct keyword in your select clause when you are selecting an aggregate (count, sum, etc). You will get distinct records.

    Next, when you have an aggregate in your select clause, you have to group by every non-aggregate field in the select clause. You have already figured that out it seems.

    To actually get what you want, query of queries seems like the best approach. Query your db for the raw data and then do a q of q to get the sum for each address. Then work with both queries to output your data as you see fit.
     
    |
    Mark as:
  • Max_Donnelly
    Currently Being Moderated
    2. Max_Donnelly,
    Jun 8, 2006 8:52 PM   in reply to Dan Bracuk
    Report
    I did try that. I'm not very good at queries of queries, evidently. I kept getting syntax errors, even though the error rendering of the SQL statement looked exactly like what I thought it should.

    Here's what I did. I changed the original query to:

    <CFQUERY name="tuccreport" datasource="#myDB#" dbtype="ODBC">
    SELECT address1, SUM(pt_count) AS Total_Patients,referrer,provider
    FROM tucc_final
    GROUP BY address1
    ORDER BY Total_Patients desc
    </cfquery>

    Then, in the output I have:

    <CFSET myCounter="1">
    <CFOUTPUT query="tuccreport">
    <CFQUERY name="referquery" dbtype="query">
    SELECT DISTINCT referrer
    FROM tuccreport
    WHERE address1=#address1#
    </cfquery>
    <tr>
    <td id="data0" valign="top">#myCounter#</td>
    <td id="data1" valign="top">#address1#</td>
    <td id="data2" valign="top">#Total_Patients#</td>
    <td id="data3" valign="top"><cfloop query="referquery">#referrer#<br /></cfloop></td>
    <td id="data4" valign="top"></td>
    </tr>
    <CFSET myCounter=(#myCounter#+1)>
    </CFOUTPUT>

    When I run the page, I get a CF Error with the following:

    Query Of Queries syntax error.
    Encountered "S.

    The error occurred in D:\INETPUB\morethanpr\clients\tucc\tucc.cfm: line 151

    149 : SELECT DISTINCT referrer
    150 : FROM tuccreport
    151 : WHERE address1=#address1#
    152 : </cfquery>
    153 : <tr>

    SQL SELECT DISTINCT referrer FROM tuccreport WHERE address1=3425 S CLARKSON ST

    Now, as far as I can tell, the "WHERE" construct shown in the error report above is exactly what I want.

    I'm at a loss. I do appreciate your input.

    Thanks,
    max
     
    |
    Mark as:
  • depli
    Currently Being Moderated
    3. depli,
    Jun 8, 2006 9:20 PM   in reply to Max_Donnelly
    Report
    <CFSET myCounter="1">
    <CFOUTPUT query="tuccreport">
    <CFQUERY name="referquery" dbtype="query">
    SELECT DISTINCT referrer
    FROM tuccreport
    WHERE address1='#address1#'
    </cfquery>
     
    |
    Mark as:
  • Max_Donnelly
    Currently Being Moderated
    4. Max_Donnelly,
    Jun 8, 2006 10:42 PM   in reply to depli
    Report
    I could have sworn I tried every iteration of quotes, single quotes, etc.

    Well, that definitely got rid of the q of q Syntax error. Tragically, I'm back where I started, though.

    The results are coming back EXACTLY how I want them, only it is breaking up the address groups for some inexplicable reason.

    Here's where the code ended up. My initial query:

    <CFQUERY name="tuccreport" datasource="#myDB#" dbtype="ODBC">
    SELECT address1, SUM(pt_count) AS Total_Patients,referrer,provider
    FROM tucc_final
    GROUP BY address1,referrer,provider
    ORDER BY Total_Patients desc
    </cfquery>

    My output:

    <CFSET myCounter="1">
    <CFOUTPUT query="tuccreport" group="address1">
    <CFQUERY name="referquery" dbtype="query">
    SELECT DISTINCT referrer
    FROM tuccreport
    WHERE address1='#address1#'
    </cfquery>
    <CFQUERY name="providerquery" dbtype="query">
    SELECT DISTINCT provider
    FROM tuccreport
    WHERE address1='#address1#'
    </cfquery>
    <tr>
    <td id="data0" valign="top">#myCounter#</td>
    <td id="data1" valign="top">#address1#</td>
    <td id="data2" valign="top">#Total_Patients#</td>
    <td id="data3" valign="top"><cfloop query="referquery">#REReplace("#referrer#",",(.*)","")#<br /></cfloop></td>
    <td id="data4" valign="top"><cfloop query="providerquery">#REReplace("#provider#",",(.*)","")#<br /></cfloop></td>
    </tr>
    <CFSET myCounter=(#myCounter#+1)>
    </CFOUTPUT>

    I really can't figure out why it decides to break the results up the way it does. In the output, the first entry is 3425 S XXX St, and it shows 87 patient referrals from that address, with the correct list of docs. Then, 2 rows down the same address repeats, with the same groups of docs. Then again in row 5 and row 11 the 3425 S XXX address comes up. The idea is to consolidate all those into one row, with one total for patient referrals.

    Very strange.

    Thanks,
    Max
     
    |
    Mark as:
  • Max_Donnelly
    Currently Being Moderated
    5. Max_Donnelly,
    Jun 8, 2006 11:06 PM   in reply to Max_Donnelly
    Report
    OK. I got it working. For the record, here's how I did it. If somebody sees something to improve on, please speak up!

    My initial queries:

    <CFQUERY name="tuccreport" datasource="#myDB#" dbtype="ODBC">
    SELECT address1, SUM(pt_count) AS Total_Patients
    FROM tucc_final
    GROUP BY address1
    ORDER BY Total_Patients desc
    </cfquery>

    <CFQUERY name="tuccrefer" datasource="#myDB#" dbtype="ODBC">
    SELECT DISTINCT referrer,address1
    FROM tucc_final
    </cfquery>

    <CFQUERY name="tuccprovider" datasource="#myDB#" dbtype="ODBC">
    SELECT DISTINCT provider,address1
    FROM tucc_final
    </cfquery>

    And my output:

    <CFSET myCounter="1">
    <CFOUTPUT query="tuccreport" group="address1">
    <CFQUERY name="referquery" dbtype="query">
    SELECT referrer
    FROM tuccrefer
    WHERE address1='#tuccreport.address1#'
    </cfquery>
    <CFQUERY name="providerquery" dbtype="query">
    SELECT provider
    FROM tuccprovider
    WHERE address1='#tuccreport.address1#'
    </cfquery>
    <tr>
    <td id="data0" valign="top">#myCounter#</td>
    <td id="data1" valign="top">#address1#</td>
    <td id="data2" valign="top">#Total_Patients#</td>
    <td id="data3" valign="top"><cfloop query="referquery">#REReplace("#referrer#",",(.*)","")#<br /></cfloop></td>
    <td id="data4" valign="top"><cfloop query="providerquery">#REReplace("#provider#",",(.*)","")#<br /></cfloop></td>
    </tr>
    <CFSET myCounter=(#myCounter#+1)>
    </CFOUTPUT>

    This proved a pleasant little misery of learning about queries of queries. I appreciate the coaching I got.

    Thanks,
    max
     
    |
    Mark as:
  • The ScareCrow
    Currently Being Moderated
    6. The ScareCrow,
    Jun 8, 2006 11:44 PM   in reply to Max_Donnelly
    Report
    Just going to throw my 2cents in here.

    <CFQUERY name="tuccreport" datasource="#myDB#" dbtype="ODBC">
    SELECT referrer, provider, st.address1, st.Total_Patients
    FROM tucc_final Inner Join (address1, SUM(pt_count) AS Total_Patients From tucc_final GROUP BY address1) As st
    On tucc_final.address1 = st.address1
    ORDER BY st.Total_Patients desc
    </cfquery>

    Then use the group attribute of cfoutput when outputting results.

    Ken
     
    |
    Mark as:
  • Max_Donnelly
    Currently Being Moderated
    7. Max_Donnelly,
    Jun 9, 2006 12:25 PM   in reply to The ScareCrow
    Report
    I had a feeling there was some kind of fancy SQL query that would be more efficient. Unfortunately, when I run the one suggested by Ken (above), I get an error. Here's my SQL query:

    <CFQUERY name="tuccreport" datasource="#myDB#" dbtype="ODBC">
    SELECT referrer, provider, st.address1, st.Total_Patients
    FROM tucc_final
    INNER JOIN
    (address1, SUM(pt_count) AS Total_Patients FROM tucc_final GROUP BY address1) AS st
    ON tucc_final.address1 = st.address1
    ORDER BY st.Total_Patients desc
    </cfquery>

    The error:

    Syntax error or access violation: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SUM(pt_count) AS Total_Patients FROM tucc_final GROUP BY address1) AS st ON tucc' at line 4

    If anyone sees anything obviously wrong with my query, I'm still interested in improving the efficiency of my code. I only partly understand the intricacies of the SQL statement Ken suggested.

    Thanks,
    max
     
    |
    Mark as:
  • Dan Bracuk
    Currently Being Moderated
    8. Dan Bracuk,
    Jun 9, 2006 12:34 PM   in reply to Max_Donnelly
    Report
    You are missing the word 'select' in your subquery.
     
    |
    Mark as:
  • philh
    Currently Being Moderated
    9. philh,
    Jun 9, 2006 12:35 PM   in reply to Max_Donnelly
    Report
    Hi Max,

    I think he missed just one statement:

    SELECT referrer, provider, st.address1, st.Total_Patients
    FROM tucc_final Inner Join ( SELECT address1, SUM(pt_count) AS Total_Patients From tucc_final GROUP BY address1) As st
    On tucc_final.address1 = st.address1
    ORDER BY st.Total_Patients desc

    HTH,
     
    |
    Mark as:
  • Max_Donnelly
    Currently Being Moderated
    10. Max_Donnelly,
    Jun 10, 2006 8:32 AM   in reply to philh
    Report
    Thanks Phil and Dan and Ken, that is definitely a more efficient query. I can tell by the speed at which the page loads compared to my 3 query combo with loops and a twist.

    The only problem in the result set is that it fails to isolate distinct providers and referrers, so in my display I end up with multiple instances of each referring/providing doc's name in each listing. I tried introducing DISTINCT into the SELECT statement, and tried adding the GROUP BY referrer, provider at the end of the query, but it didn't seem to impact the final result. Here's where I ended up, for the record. If you see a way to utilize this more efficient query and also successfully eliminate duplicate records when it comes to referrer and provider names, please let me know.

    <CFQUERY name="tuccreport" datasource="#myDB#" dbtype="ODBC">
    SELECT referrer, provider, st.address1, st.Total_Patients
    FROM tucc_final
    INNER JOIN
    (SELECT address1, SUM(pt_count) AS Total_Patients FROM tucc_final GROUP BY address1) AS st
    ON tucc_final.address1 = st.address1
    GROUP BY referrer, provider
    ORDER BY st.Total_Patients desc
    </cfquery>

    My output:

    <CFSET myCounter="1">
    <CFOUTPUT query="tuccreport" group="address1">
    <tr>
    <td id="data0" valign="top">#myCounter#</td>
    <td id="data1" valign="top">#address1#</td>
    <td id="data2" valign="top">#Total_Patients#</td>
    <td id="data3" valign="top"><CFOUTPUT group="referrer">#REReplace("#referrer#",",(.*)","")#<br /></CFOUTPUT></td>
    <td id="data4" valign="top"><CFOUTPUT group="provider">#REReplace("#provider#",",(.*)","")#<br /></CFOUTPUT></td>
    </tr>
    <CFSET myCounter=(#myCounter#+1)>
    </CFOUTPUT>

    I think this is a great exercise in INNER JOIN queries. I never in a million years would have come up with the query Ken recommended. I'm going to continue to puzzle over it to make sure I understand how it works. I think it will come in very handy in the future.

    Thanks again!
    max
     
    |
    Mark as:
  • The ScareCrow
    Currently Being Moderated
    11. The ScareCrow,
    Jun 10, 2006 6:29 PM   in reply to Max_Donnelly
    Report
    First, sorry about missing the select keyword.

    As I don't know the data, it's a bit hard, but
    I assume that a provider can have more than one address and referer, so I think all you need to do is move the goups around

    First group by the provider, then the referer, then the address

    Ken
     
    |
    Mark as:
  • philh
    Currently Being Moderated
    12. philh,
    Jun 12, 2006 9:27 AM   in reply to The ScareCrow
    Report
    I think it would help to understand the functionality of CFOUTPUT by group.

    Take this sample data:
    company - product
    Joe's Flowers - begonias
    Joe's Flowers - asters
    Joe's Flowers - tulips
    Mary's Florist Service - gardenias
    Mary's Florist Service - daffodils
    Mary's Florist Service - pansies


    You want output to read:

    Joe's Flowers
    - begonias
    - asters
    - tulips

    Mary's Florist Service
    - gardenias
    - daffodils
    - pansies

    To use CFOUTPUT with the group:

    <CFOUTPUT group="company">
    #company#
    <CFOUTPUT>
    - #product#
    </CFOUTPUT>
    </CFOUTPUT>

    The inner CFOUTPUT section runs as long as the outer value (company) does not change. This is why it's important to order your query results properly. "GROUP" is sort of a misnomer in that it has nothing to do with the SQL GROUP BY statement; it's all about how you ORDER the results.

    HTH,
     
    |
    Mark as:
Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points