Sum formula???

[christrinder]

Hello,

In the past I've been able to add up the required values by using:
Select sum(var) AS total FROM table WHERE var2 = x

But now I need to add up the variables in a table where the criteria is in another, linked table. Basically I just want the sum of the [CJB_InsertDates.Profit] in the query below! Any ideas?

Thanks,
Chris

- - - - - - -

SELECT CJB_JobRequest.RequestID, CJB_JobRequest.JobName, CJB_InsertDates.Profit, CJB_InsertDates.Archive
FROM CJB_JobRequest INNER JOIN CJB_InsertDates ON CJB_JobRequest.RequestID = CJB_InsertDates.RequestID WHERE CJB_InsertDates.Archive = 1

[raf]

Not simply:
SELECT CJB_JobRequest.RequestID, CJB_JobRequest.JobName, sum(CJB_InsertDates.Profit) AS totalsum, CJB_InsertDates.Archive
FROM CJB_JobRequest INNER JOIN CJB_InsertDates ON CJB_JobRequest.RequestID = CJB_InsertDates.RequestID WHERE CJB_InsertDates.Archive = 1

If i'm not mistaking, the join and where clause will create a view (temporarely table created by the rdbm) and the aggregation function will run on that view and give you what you desire (well ...)
<edit>alias for sum() added</edit>

[christrinder]

Thanks Raf. I'll try it but I think I already have and it gave me an error message... something to do with an aggregate function. WIll have another look at work tomorrow and post the error message if I can't get it to work.

Thanks again,
Chris

[christrinder]

Hello. Still can't get it to work. I get this error meesage.

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'RequestID' as part of an aggregate function.

/creative/management/Report_results.asp, line 59


Line 59 is the StrSQL statsment, shown in full below:

strSQLjobs2 = "SELECT TOP 200 CJB_JobRequest.RequestID, CJB_JobRequest.JobName, CJB_JobRequest.JobDate, CJB_JobRequest.TypeID, CJB_Type.Type, DSNLocations.RecordID, DSNLocations.SupplierName1, DSNCompanies.CompanyID, DSNCompanies.CompanyName, CJB_Publication.PublicationID, CJB_Publication.Publication, CJB_InsertDates.InsertID, CJB_InsertDates.InsertDate, CJB_InsertDates.InvoiceNo, CJB_InsertDates.Cost, CJB_InsertDates.Discount, sum(CJB_InsertDates.Profit) AS totalprofit, CJB_InsertDates.Invoiced, CJB_DesignJobs.WorkedHours, Users.Name FROM CJB_Type INNER JOIN (Users INNER JOIN ((CJB_Publication INNER JOIN (DSNCompanies INNER JOIN (DSNLocations INNER JOIN (CJB_JobRequest INNER JOIN CJB_InsertDates ON CJB_JobRequest.RequestID = CJB_InsertDates.RequestID) ON DSNLocations.RecordID = CJB_JobRequest.LocationID) ON DSNCompanies.CompanyID = DSNLocations.CompanyID) ON CJB_Publication.PublicationID = CJB_JobRequest.PublicationID) INNER JOIN CJB_DesignJobs ON CJB_InsertDates.InsertID = CJB_DesignJobs.InsertID) ON Users.UserID = CJB_DesignJobs.UserID) ON CJB_Type.TypeID = CJB_JobRequest.TypeID WHERE DSNLocations.SupplierName1 LIKE '%"& request.form("Dealership") &"%' AND DSNCompanies.CompanyName LIKE '%"& request.form("Company") &"%' AND CJB_Publication.Publication LIKE '%"& request.form("Publication") &"%' AND CJB_Type.Type LIKE '%"& request.form("Type") &"%' AND (CJB_JobRequest.JobDate > #"& request.form("date1") &"# AND CJB_JobRequest.JobDate < #"& request.form("date2") &"#) AND Invoiced = 1"
Set objRSjobs2 = oConnection.Execute(strSQLjobs2)


Any ideas? Please?

[raf]

Hmm. Of coarse. Aggregationfunction like 'sum()' only work if they are the only outputted field or if they are used in group by statement. Kinda big join, so i don't know what comes out (or should come out), but you could ad a group by clause at the end, to get the sum for each group.

There's also another problem : the TOP predicate requires an order by clause, since it runs on the view ! It simply takes the 200 first records from the ordere view.

strSQLjobs2 = "SELECT TOP 200 CJB_JobRequest.RequestID, CJB_JobRequest.JobName, CJB_JobRequest.JobDate, CJB_JobRequest.TypeID, CJB_Type.Type, DSNLocations.RecordID, DSNLocations.SupplierName1, DSNCompanies.CompanyID, DSNCompanies.CompanyName, CJB_Publication.PublicationID, CJB_Publication.Publication, CJB_InsertDates.InsertID, CJB_InsertDates.InsertDate, CJB_InsertDates.InvoiceNo, CJB_InsertDates.Cost, CJB_InsertDates.Discount, sum(CJB_InsertDates.Profit) AS totalprofit, Count(*) AS checkgrouping, CJB_InsertDates.Invoiced, CJB_DesignJobs.WorkedHours, Users.Name FROM CJB_Type INNER JOIN (Users INNER JOIN ((CJB_Publication INNER JOIN (DSNCompanies INNER JOIN (DSNLocations INNER JOIN (CJB_JobRequest INNER JOIN CJB_InsertDates ON CJB_JobRequest.RequestID = CJB_InsertDates.RequestID) ON DSNLocations.RecordID = CJB_JobRequest.LocationID) ON DSNCompanies.CompanyID = DSNLocations.CompanyID) ON CJB_Publication.PublicationID = CJB_JobRequest.PublicationID) INNER JOIN CJB_DesignJobs ON CJB_InsertDates.InsertID = CJB_DesignJobs.InsertID) ON Users.UserID = CJB_DesignJobs.UserID) ON CJB_Type.TypeID = CJB_JobRequest.TypeID WHERE DSNLocations.SupplierName1 LIKE '%"& request.form("Dealership") &"%' AND DSNCompanies.CompanyName LIKE '%"& request.form("Company") &"%' AND CJB_Publication.Publication LIKE '%"& request.form("Publication") &"%' AND CJB_Type.Type LIKE '%"& request.form("Type") &"%' AND (CJB_JobRequest.JobDate > #"& request.form("date1") &"# AND CJB_JobRequest.JobDate < #"& request.form("date2") &"#) AND Invoiced = 1 GROUP BY CJB_JobRequest.RequestID, CJB_JobRequest.JobName, CJB_JobRequest.JobDate, CJB_JobRequest.TypeID, CJB_Type.Type, DSNLocations.RecordID, DSNLocations.SupplierName1, DSNCompanies.CompanyID, DSNCompanies.CompanyName, CJB_Publication.PublicationID, CJB_Publication.Publication, CJB_InsertDates.InsertID, CJB_InsertDates.InsertDate, CJB_InsertDates.InvoiceNo, CJB_InsertDates.Cost, CJB_InsertDates.Discount, CJB_InsertDates.Invoiced, CJB_DesignJobs.WorkedHours, Users.Name ORDER BY CJB_JobRequest.RequestID desc"

I included a count. If you display that variable, you can see if any records were actualy grouped (since there are so many variables where is grouped on, that is not very likely, but still. After checking the count on a few runs, it can be omitted (unless it's usefull).
This statement will give you about 200 records (depends on the values in the orber by variable). Buth the sum will the same as CJB_InsertDates.Profit (since no records will actually be grouped), and i don't think that this is what you want.

I assume you want the sum of the profit of the top 200 records (top 200 accordingly to some criterium --> the order by variable and order). This requires another query then the one you have. It is also something completely different as the data you have in your current recordset. This sum is a value that is not record-specific, it's an aggregated value for the values of all records in the recordset. I guess you need something like
sql="select sum(variable) as thesum from (SELECT TOP 200 CJB_InsertDates.Profit, CJB_JobRequest.RequestID FROM CJB_Type INNER JOIN (Users INNER JOIN ((CJB_Publication INNER JOIN (DSNCompanies INNER JOIN (DSNLocations INNER JOIN (CJB_JobRequest INNER JOIN CJB_InsertDates ON CJB_JobRequest.RequestID = CJB_InsertDates.RequestID) ON DSNLocations.RecordID = CJB_JobRequest.LocationID) ON DSNCompanies.CompanyID = DSNLocations.CompanyID) ON CJB_Publication.PublicationID = CJB_JobRequest.PublicationID) INNER JOIN CJB_DesignJobs ON CJB_InsertDates.InsertID = CJB_DesignJobs.InsertID) ON Users.UserID = CJB_DesignJobs.UserID) ON CJB_Type.TypeID = CJB_JobRequest.TypeID WHERE DSNLocations.SupplierName1 LIKE '%"& request.form("Dealership") &"%' AND DSNCompanies.CompanyName LIKE '%"& request.form("Company") &"%' AND CJB_Publication.Publication LIKE '%"& request.form("Publication") &"%' AND CJB_Type.Type LIKE '%"& request.form("Type") &"%' AND (CJB_JobRequest.JobDate > #"& request.form("date1") &"# AND CJB_JobRequest.JobDate < #"& request.form("date2") &"#) AND Invoiced = 1 ORDER BY CJB_JobRequest.RequestID desc)"

(You can probably do with less joins. This query will return one record with one value)

So, it depends on what you're trying to do. If you just wan't to display this sum, run something like the last statement (the second select should have only the joins to get the right records selected)
If you want to display these 200 top records, then just throw out the sum function and group by clause, and compute it yourself inside the loop to display the records (since you have to loop throughthem anyway, it's no big deal to have a value that is inceremented by the profit value on each loop.
If you wan't to display some of the records + the sum, you'll probably have to run 2 querys.
If you wan't to group the records and compute a sum for each groep, then you should only include the grouped variabels in the statement.

[christrinder]

EXCELLENT!

I so didn't even think about creating an incremental variable inside the Loop... it did the job perfectly. Thanks so much Raf, you've made my day... AGAIN!

Regards,
Chris

[raf]

Glad you got it solved. Costed you some reading :D .
(It's alway's these little things)