 |
BorlandTalk.com
Borland discussion newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
P.S.Bell
Guest
|
 Posted: Fri Feb 03, 2006 10:08 pm Post subject: sum of a function |
 |
|
I have a function that returns a for each record that I want to
sum.
The following query gives me summed results for each record in
licences held
SELECT LicencesHeld.LicNo, LicenceTypes.LicenceType,
LicencesHeld.LicName, LicencesHeld.AreaCode, Areas.AreaManager,
Areas.AreaName,
SUM(ItemActions.VatTotalSales) AS TotalVAT,
round(SUM(ItemActions.CHNet),2,0) AS TotalNet,
round(sum(itemactions.chnet * jobs.appliedfeerate)/100,2,0)
as Fees,
FROM ItemActions INNER JOIN
Items ON ItemActions.ItemID = Items.ItemID RIGHT OUTER JOIN
LicencesHeld LEFT OUTER JOIN
LicenceTypes ON LicenceTypes.LicenceTypeID
=LicencesHeld.LicTypeID LEFT OUTER JOIN
Jobs ON Jobs.LicID = LicencesHeld.LicID ON Items.JobNo =
Jobs.JobNo Left Join
jobstatus on Jobs.JobStatusID=Jobstatus.JobStatusID LEFT
JOIN
Areas on LicencesHeld.AreaCode=Areas.AreaID
WHERE
(Jobs.FinalReport BETWEEN CONVERT(DATETIME, '01/01/05' ,3)
AND
CONVERT(DATETIME, '01/01/10',3)) AND JobStatus.JobStatus =
'Finalised'
GROUP BY LicencesHeld.LicNo,
LicenceTypes.LicenceType,Areas.AreaManager, Areas.AreaName,
LicencesHeld.LicName, LicencesHeld.AreaCode
but if I add
(select dbo.fn_allocationfee(Jobs.JobNo,Jobs.ReportOnly))
I have to add 'Jobs.JobNo,Jobs.ReportOnly' to group by and I get
a results for each row of Jobs. No surprise I hear, but what I
was actually trying to do was something like
(sum(select dbo.fn_allocationfee(Jobs.JobNo,Jobs.ReportOnly)))
and get the summed function results per row of licences held.
Pat Bell |
|
| Back to top |
|
 |
Oliver Townshend
Guest
|
| Posted: Mon Feb 13, 2006 12:03 pm Post subject: Re: sum of a function |
|
|
| Quote: | The following query gives me summed results for each record in
licences held
....
(sum(select dbo.fn_allocationfee(Jobs.JobNo,Jobs.ReportOnly)))
|
Well I think you need to do a correlated sub-query to give the sum, an
example of which would be something like (i'm not prepared to rewrite your
query at this point, but give me a yell if you can't):
select a, (select sum(b) from table t1 where t1.key=t.key)
from table t
group by a
Oliver Townshend |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
