[thelist] SQL 'Buckets' - Was: way OT -- keyboard woes

rudy r937 at interlog.com
Tue Oct 16 23:15:23 CDT 2001


> Could you explain 'buckets' please? Are they the
> fourth column in the two subqueries you give as an example?

hi andrew

no, they'd be script variables where you accumulate the running totals
yourself ("add to the bucket") as you display each detail line from the
query

so if your query was

   <cfquery name="details">
      select col1, col2, col1+col2 as rowsum
          from yourtable
      order by 1
   </cfquery>

then to "add to the buckets" you'd do something like this --

   <cfset col1total=0>
   <cfset col2total=0>
   <cfset rowsumtotal=0>
   <cfoutput>
   <cfloop query="details">
         <td>detail line:</td>
             <td>#col1#</td><td>#col1#</td><td>#rowsum#</td>
         <cfset col1total=col1total+col1>
         <cfset col2total=col2total+col2>
         <cfset rowsumtotal=rowsumtotal+rowsum>
   </cfloop>
         <td>total line:</td>
             <td>#col1total#</td><td>#col1total#</td><td>#rowsumtotal#</td>
   </cfoutput>

whereas if you let the database hand you the totals row, the query is a bit
longer

   <cfquery name="detailsplustotals">
      select 'detail line:' as rowtype
           , col1, col2, col1+col2 as rowsum
         from yourtable
    union all
      select 'total line:'
           , sum(col1), sum(col2), sum(col1)+sum(col2)
        from yourtable
    order by 1,2
   </cfquery>

but the output is hellishly simpler --

   <cfoutput query="detailsplustotals">
         <td>#rowtype#</td>
             <td>#col1#</td><td>#col1#</td><td>#rowsum#</td>
   </cfoutput>

neat, eh?


>And while I'm asking silly questions: can you recommend a simple way
>to give each row in a select a row number? I can retrieve by row
>number with mysql, but it'd be really handy if I could generate an
>actual column with a ranking -- but I'm buggered if I can see how. My
>mind must be going down the same road as your keyboard.

ah, rankings...... [sigh]

no, there is no simple way

it would be easiest to use an auto_increment field and just display that


since you mentioned mysql, i can tell you right now that because you can't
use subqueries, the following technique isn't going to work for you...

http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_ci
d404894_tax285649,00.html


rudy






More information about the thelist mailing list