Sum of values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Sum of values

Author

Topic

php95saj
Starting Member

43 Posts

Posted - 2002-06-25 : 07:45:12

I am having problem calculating sums of differenct values, appreciate if anyone can help me out.
Here is the problem:

The QUERY:
SELECT COUNT(tblJobs.jobID) AS jobsThisMonth,
SUM(tblJobs.translationCost + tblJobs.additionalCost + tblJobs.totalVAT)
AS totalCost, SUM(tblJobCosts.wordCount)
AS totalWordsThisMonth, tblJobs.currencyID,
tblJobs.serviceLevelID, tblJobs.jobCompletionDate

FROM tblJobs INNER JOIN
tblJobCosts ON tblJobs.jobID = tblJobCosts.jobID

WHERE tblJobs.jobCompletionDate IS NOT NULL AND
DATEPART(m, tblJobs.jobCompletionDate) = 6 AND
DATEPART(yyyy, tblJobs.jobCompletionDate) = 2002

GROUP BY tblJobs.jobCompletionDate, tblJobs.currencyID,
tblJobs.serviceLevelID

ORDER BY tblJobs.jobCompletionDate, tblJobs.serviceLevelID,
tblJobs.currencyID

THE RESULT:

Total Jobs Total Cost Total Words Currency Service Level
2 130.9 5064 3 1
3 210.96 843 1 1
2 93.76 562 1 1
1 26.65 255 3 1
1 23.44 281 1 1
1 23.44 2 1 1
6 843.9 1686 1 1
1 23.44 281 1 1
1 26.65 357 3 1
1 23.44 2 1 1
1 23.44 281 1 1

While I want to display results something like:

Total Jobs, total words, total cost when:

service level is 1 and currency is 1
service level is 2 and currency is 1
service level is 3 and currency is 1

Total Jobs, total words, total cost when:
service level is 1 and currency is 2
service level is 2 and currency is 2
service level is 3 and currency is 2

Total Jobs, total words, total cost when:
service level is 1 and currency is 3
service level is 2 and currency is 3
service level is 3 and currency is 3

Thanks.

MakeYourDaddyProud

184 Posts

Posted - 2002-06-25 : 07:56:18

I think your CompletionDate is throwing your sort order off balance. Are you really wanting the completion date in the grouping? I ask this because you are getting a result set that is primarily ordered by each 'date tick' down to milliseconds, having the net effect of splitting out your sort into single tuple tblJobs.currencyID and
tblJobs.serviceLevelIDs. I think you can simply remove the date (tblJobs.jobCompletionDate) from the select list, group by and order by and you will be fine.

Dan
www.danielsmall.com IT Factors

<<monet makes money>>

php95saj
Starting Member

43 Posts

Posted - 2002-06-25 : 08:04:40

Thank you. It has worked.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources