Hi Claude
If you want quick and nasty without a second query you could try
select
tt.*, tcost.total
from
transaction_table tt,
(
select sum(cost)
from transaction_table
where transaction_month = 'December' /* Same as like with no % */
) as tcost
where
transaction_month = 'December'
This will do a cartesian join on the single row in the subquery (treated as
a veiw so only called once) and the results so each row will have the total
cost. The other way would be a second query that gets the total cost
(basically the same as what I've done)
Shane
On 3/8/07, M@ Bourke <[EMAIL PROTECTED]> wrote:
>
> Hi Claude,
>
> This isn't syntactically best practice but if I understand your question
> do.
>
> Select fieldnames (Select sum(cost) as total_cost
> from transactions_table
> where transaction_month like 'December') AS total_cost
> from transactions_table
> where transaction_month like 'December'
>
> this way every total_cost in the output will be the same
>
> M@
>
>
> On 3/7/07, claude raiola <[EMAIL PROTECTED]> wrote:
> >
> >
> > Hi
> >
> > I have a query and I am wanting to determine the total sum of the cost
> > field for all those matching records so that I can determine the total
> > amount of cost of all records found by the query
> >
> > I know I can create the formula to add the values through each pass of
> > the output of the cfloop of the query, however I am wondering if I can do
> > the same within the query eg
> >
> > <cfquery name=monthly_cost>
> > Select sum(cost) as total_cost,*
> > from transactions_table
> > where transaction_month like 'December'
> > </cfquery>
> >
> > so the the value #monthly_cost.total_cost# has value of the sum of all
> > cost values from the records found in the query
> >
> >
> >
> >
> >
> > Kind Regards
> >
> > Claude Raiola
> > B.Econ(Acc.); B.Hotel Mngt.
> > Mobile: 0414 228 948
> > Phone: 07 5527 1990
> > Fax: 07 3319 6444
> >
> > Websites:
> > www.WebsiteSolutions.com.au <http://www.websitesolutions.com.au/>
> > www.AustralianAccommodation.com<http://www.australianaccommodation.com/>
> > www.AccommodationNewZealand.com<http://www.accommodationnewzealand.com/>
> > www.HospitalityPurchasing.net <http://www.hospitalitypurchasing.net/>
> > www.Samaris.net <http://www.samaris.net/>
> >
> >
> >
> >
> >
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"cfaussie" group.
To post to this group, send email to cfaussie@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cfaussie?hl=en
-~----------~----~----~----~------~----~------~--~---
