SQL Aggregate Function without using Group By

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)

SQL Aggregate Function without using Group By

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-12 : 09:06:10

Cathy writes "I have the following sql statements (one before the do loop, and 5 after), which I would like to join. When running the script as is, when in the 'do until' loop the server times out. When I combine the two statements, it requires a 'group by' by all the variables that are NOT involved in an aggregate function. This does not give me the results I want. Everything should take the direction of the j.jobid. I need to be able to combine these statements, without using the 'group by'. Alot of our reports are run this way, and I can't seem to find any help anywhere on a better way. Thank you.

sqlStmt = "select j.jobid, j.clientid, j.csrrepid, j.salesrepid, " _
& "j.joborderdate, j.description, j.estimateddeliverydate, j.commissionid, " _
& "a.companyname, b.firstname, b.lastname " _
& "from job j inner join (client a inner join userprofile b on a.userprofileid = b.userprofileid) on a.clientid = j.clientid " _
& "where j.statusid = 4 "
sqlstmt = sqlstmt & RepIDClause & CSRIDClause & " order by j.jobid "
set rsSalesWIP = dbconnection.execute(Sqlstmt)

do until rsSalesWIP.eof
JobID = rsSalesWIP("JobID")
ClientID = rsSalesWIP("ClientID")

sqlstmt = "select sum(totalextension) as SalesTotal from salesorder where jobid = " & JobID
set rsSalesTotal = dbconnection.execute(sqlstmt)

sqlstmt = "select sum(estimatedartcost) as ArtTotal from salesorder where jobid = " & JobID
set rsArtTotal = dbconnection.execute(sqlstmt)

sqlstmt = "select sum(TotalExtention) as CostTotal from purchase_order where jobid = " & JobID
set rsCostTotal = dbconnection.execute(sqlstmt)

sqlstmt = "select sum(amount) as MiscCostTotal from MiscCost where jobid = " & JobID
set rsMiscCostTotal = dbconnection.execute(sqlstmt)

sqlstmt = "select sum(amount) as ArtCostTotal from ArtworkCost where jobid = " & JobID
set rsArtCostTotal = dbconnection.execute(sqlstmt)

etc..."

Peter Dutch
Posting Yak Master

127 Posts

Posted - 2002-04-12 : 10:06:24

The following should give you the general idea, you just have to add the other JOINed tables.


SELECT J.JobID, J.CLientID, J.csrrepID, J.Saledrepid, A.SalesTotal,A.ArtTotal,A.CostTotal [etc]
FROM Jobs J
INNER JOIN
(
SELECT JobID,sum(totalextension) as SalesTotal, sum(estimatedartcost) as ArtTotal ,sum(TotalExtention) as CostTotal 
FROM salesorder 
GROUP BY JobID
) AS A
ON J.JobID = A.JobID

Edited by - Peter Dutch on 04/12/2002 10:08:31

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-12 : 10:08:41

Just combine the subqueries into the original query:

sqlStmt = "select j.jobid, j.clientid, j.csrrepid, j.salesrepid, "
sqlStmt = sqlStmt & "j.joborderdate, j.description, j.estimateddeliverydate, j.commissionid, "
sqlStmt = sqlStmt & " a.companyname, b.firstname, b.lastname, "
sqlStmt = sqlStmt & " S.SalesTotal, S.ArtTotal, P.CostTotal, M.MiscCostTotal, Art.ArtCost Total "
sqlStmt = sqlStmt & " from job j inner join client a on a.clientid = j.clientid
sqlStmt = sqlStmt & " inner join userprofile b on a.userprofileid = b.userprofileid "
sqlStmt = sqlStmt & " inner join (select JobID, sum(totalextension) as SalesTotal, sum(estimatedartcost) AS ArtTotal from salesorder GROUP BY JobID) S ON J.jobid=S.jobid) "
sqlStmt = sqlStmt & " inner join (select JobID, sum(TotalExtention) as CostTotal from purchase_order GROUP BY JobID) P ON J.JobID=P.JobID "
sqlStmt = sqlStmt & " inner join (select JobID, sum(amount) as MiscCostTotal from MiscCost
GROUP BY JobID) M ON J.JobID=M.JobID "
sqlStmt = sqlStmt & " inner join (select JobID, sum(amount) as ArtCostTotal from ArtworkCost GROUP BY JobID) Art ON J.JobID=Art.JobID "
sqlStmt = sqlStmt & "where j.statusid = 4 "
sqlstmt = sqlstmt & RepIDClause & CSRIDClause & " order by j.jobid "
set rsSalesWIP = dbconnection.execute(Sqlstmt)

You did not say if you were using SQL Server or MS Access. This will work in SQL Server, but may not work in Access.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources