[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