Re: [ACFUG Discuss] group count ability
In practice, I've not seen using count(1) instead of count(*) to make any actual difference. It may be how our databases are configured (Oracle 9i). We're moving to 10g soon, so I'm looking forward to see if that makes a difference. I would encourage you to test and compare the two before just changing your code.Scott C.On 8/18/06, Fennell, Mark P. < [EMAIL PROTECTED]> wrote:OT point of clarity... The select count(*) as my_total from my_table in oracle reads every column of every row. For performance, it is recommended that you use select count(1) as my_total from my_table since that's not actually reading data but does more of a row-level existence check. Additionally, since you are not actually selecting data, the row count can be read from the table's statistics if they've been generated recently. If you are running oracle, you may also want to look into the cube() and rollup() group by functions for tallying group stats. Hope that helps.mfmark fennell, ocp dbaathens regional medical centerathens, ga-This email is intended only for the named recipient(s). It may contain information that is proprietary, confidential or otherwise prohibited from disclosure. If you are not the named addressee, you are not authorized to read, print, retain, copy or disseminate this message or any part of it. If you have received this message in error, please reply immediately by email or telephone me at 706-475-4357 and delete all copies of the message. --Original Message-From: [EMAIL PROTECTED] [mailto: [EMAIL PROTECTED]]On Behalf Of Douglas KnudsenSent: Thursday, August 17, 2006 11:07 PMTo: discussion@acfug.orgSubject: Re: [ACFUG Discuss] group count ability what DB? If on Oracle you can add a column to your query called say countofcolumntogroupby populated by using a aggregate function as a analytic functionCOUNT(*) OVER (PARTITION BY columntogroupby ) AS countofcolumnttogeroupby no grouping needed here on the SQL side. Does SQLServer have a similar thing?nice faq on this herehttp://orafaq.com/node/55DKOn 8/17/06, Seth Tepfer < [EMAIL PROTECTED]> wrote:When doing a group on a query, is there a simple way to identify the number of rows in each group/section? That is, rather than prerunning over the query and setting up an array or structure. Perhaps something built in, even? If not, is this something worth asking for? My Section Title - there will #x# rows in this sectionA single row My section footer - there were #x# rows in this sectionOxford College Information Technology (OCIT)Now providing the same great service ina new building with the same telephones and email OCIT House603 Emory Street-To unsubscribe from this list, manage your profile @http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglistsArchive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink---Douglas Knudsenhttp://www.cubicleman.comthis is my signature, like it? -To unsubscribe from this list, manage your profile @http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglistsArchive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink--To unsubscribe from this list, manage your profile @ http://www.acfug.org?falogin.edituserformFor more info, see http://www.acfug.org/mailinglistsArchive @ http://www.mail-archive.com/discussion%40acfug.org/List hosted by http://www.fusionlink.com- - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink -
RE: [ACFUG Discuss] group count ability
OT point of clarity... The select count(*) as my_total from my_table in oracle reads every column of every row. For performance, it is recommended that you use select count(1) as my_total from my_table since that's not actually reading data but does more of a row-level existence check. Additionally, since you are not actually selecting data, the row count can be read from the table's statistics if they've been generated recently. If you are running oracle, you may also want to look into the cube() and rollup() group by functions for tallying group stats. Hope that helps. mf mark fennell, ocp dba athens regional medical center athens, ga - This email is intended only for the named recipient(s). It may contain information that is proprietary, confidential or otherwise prohibited from disclosure. If you are not the named addressee, you are not authorized to read, print, retain, copy or disseminate this message or any part of it. If you have received this message in error, please reply immediately by email or telephone me at 706-475-4357 and delete all copies of the message. - -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Douglas Knudsen Sent: Thursday, August 17, 2006 11:07 PM To: discussion@acfug.org Subject: Re: [ACFUG Discuss] group count ability what DB? If on Oracle you can add a column to your query called say countofcolumntogroupby populated by using a aggregate function as a analytic function COUNT(*) OVER (PARTITION BY columntogroupby ) AS countofcolumnttogeroupby no grouping needed here on the SQL side. Does SQLServer have a similar thing? nice faq on this here http://orafaq.com/node/55 DK On 8/17/06, Seth Tepfer <[EMAIL PROTECTED]> wrote: When doing a group on a query, is there a simple way to identify the number of rows in each group/section? That is, rather than prerunning over the query and setting up an array or structure. Perhaps something built in, even? If not, is this something worth asking for? My Section Title - there will #x# rows in this section A single row My section footer - there were #x# rows in this section Oxford College Information Technology (OCIT) Now providing the same great service in a new building with the same telephones and email OCIT House 603 Emory Street - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink - -- Douglas Knudsen http://www.cubicleman.com this is my signature, like it? - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink - - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -
RE: [ACFUG Discuss] group count ability
Return Receipt Your RE: [ACFUG Discuss] group count ability document: wasCraig Nassal/ATL/FRS received by: at:08/18/2006 08:59:52 AM - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -
Re: [ACFUG Discuss] group count ability
Slick man! New bookmark made. :) mcg Douglas Knudsen <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 08/17/2006 11:06 PM Please respond to discussion@acfug.org To discussion@acfug.org cc Subject Re: [ACFUG Discuss] group count ability what DB? If on Oracle you can add a column to your query called say countofcolumntogroupby populated by using a aggregate function as a analytic function COUNT(*) OVER (PARTITION BY columntogroupby ) AS countofcolumnttogeroupby no grouping needed here on the SQL side. Does SQLServer have a similar thing? nice faq on this here http://orafaq.com/node/55 DK On 8/17/06, Seth Tepfer <[EMAIL PROTECTED]> wrote: When doing a group on a query, is there a simple way to identify the number of rows in each group/section? That is, rather than prerunning over the query and setting up an array or structure. Perhaps something built in, even? If not, is this something worth asking for? My Section Title - there will #x# rows in this section A single row My section footer - there were #x# rows in this section Oxford College Information Technology (OCIT) Now providing the same great service in a new building with the same telephones and email OCIT House 603 Emory Street - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink - -- Douglas Knudsen http://www.cubicleman.com this is my signature, like it? - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink - - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink -
RE: [ACFUG Discuss] group count ability
If you have a GROUP BY in your SQL, see if ROLLUP/WITH ROLLUP (SQL Server/Oracle) helps you out any. mcg Charlie Arehart <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 08/17/2006 11:06 PM Please respond to discussion@acfug.org To discussion@acfug.org cc Subject RE: [ACFUG Discuss] group count ability That is a good question and would be a useful feature request, I think. I don't believe there to be any such solution for now. (For those who don't know, the CFOUTPUT GROUP is intended to be used with a query that's been ORDERed BY the named ColumnToGroupBy. It then creates in the outer loop one iteration for each unique value of that ColumnToGroupBy, and the inner CFOUTPUT pair creates a loop over every record that has that value.) You can of course easily get the count in the "footer", in that you could have a counter that's initialized in the header and tallied during the inner loop. The "header" section is tougher. The only thing I can think of in the meantime is to either a) do a query of queries (in that header section) against the MyQueryName to find the count of records with the current value or b) [and perhaps what you were alluding to] above the outer loop do a query that gets the count of records for each of all the unique values, and then do an associative array lookup of each value inside that "header" portion of the loop.. But maybe someone has a better solution. I'd be intrigued to hear. /charlie http://www.carehart.org/blog/ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Seth Tepfer Sent: Thursday, August 17, 2006 9:54 PM To: [EMAIL PROTECTED]; discussion@acfug.org Subject: [ACFUG Discuss] group count ability When doing a group on a query, is there a simple way to identify the number of rows in each group/section? That is, rather than prerunning over the query and setting up an array or structure. Perhaps something built in, even? If not, is this something worth asking for? My Section Title - there will #x# rows in this section A single row My section footer - there were #x# rows in this section Oxford College Information Technology (OCIT) Now providing the same great service in a new building with the same telephones and email OCIT House 603 Emory Street - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink - - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink - - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink -
Re: [ACFUG Discuss] group count ability
what DB? If on Oracle you can add a column to your query called say countofcolumntogroupby populated by using a aggregate function as a analytic functionCOUNT(*) OVER (PARTITION BY columntogroupby ) AS countofcolumnttogeroupby no grouping needed here on the SQL side. Does SQLServer have a similar thing? nice faq on this herehttp://orafaq.com/node/55DK On 8/17/06, Seth Tepfer <[EMAIL PROTECTED]> wrote: When doing a group on a query, is there a simple way to identify the number of rows in each group/section? That is, rather than prerunning over the query and setting up an array or structure. Perhaps something built in, even? If not, is this something worth asking for? My Section Title - there will #x# rows in this section A single row My section footer - there were #x# rows in this section Oxford College Information Technology (OCIT) Now providing the same great service in a new building with the same telephones and email OCIT House 603 Emory Street - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink - -- Douglas Knudsenhttp://www.cubicleman.comthis is my signature, like it? - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink -
RE: [ACFUG Discuss] group count ability
That is a good question and would be a useful feature request, I think. I don't believe there to be any such solution for now. (For those who don't know, the CFOUTPUT GROUP is intended to be used with a query that's been ORDERed BY the named ColumnToGroupBy. It then creates in the outer loop one iteration for each unique value of that ColumnToGroupBy, and the inner CFOUTPUT pair creates a loop over every record that has that value.) You can of course easily get the count in the "footer", in that you could have a counter that's initialized in the header and tallied during the inner loop. The "header" section is tougher. The only thing I can think of in the meantime is to either a) do a query of queries (in that header section) against the MyQueryName to find the count of records with the current value or b) [and perhaps what you were alluding to] above the outer loop do a query that gets the count of records for each of all the unique values, and then do an associative array lookup of each value inside that "header" portion of the loop.. But maybe someone has a better solution. I'd be intrigued to hear. /charlie http://www.carehart.org/blog/ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Seth TepferSent: Thursday, August 17, 2006 9:54 PMTo: [EMAIL PROTECTED]; discussion@acfug.orgSubject: [ACFUG Discuss] group count ability When doing a group on a query, is there a simple way to identify the number of rows in each group/section? That is, rather than prerunning over the query and setting up an array or structure. Perhaps something built in, even? If not, is this something worth asking for?My Section Title - there will #x# rows in this sectionA single rowMy section footer - there were #x# rows in this section Oxford College Information Technology (OCIT)Now providing the same great service in a new building with the same telephones and email OCIT House603 Emory Street - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink - - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink -
[ACFUG Discuss] group count ability
When doing a group on a query, is there a simple way to identify the number of rows in each group/section? That is, rather than prerunning over the query and setting up an array or structure. Perhaps something built in, even? If not, is this something worth asking for? My Section Title - there will #x# rows in this section A single row My section footer - there were #x# rows in this section Oxford College Information Technology (OCIT) Now providing the same great service in a new building with the same telephones and email OCIT House 603 Emory Street - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink -
