Help on Aggregation please

[ukmikeb]

I would be most grateful if someone could help me with the following problem which I would have been able to do in a flash 30 years ago!

I have a table which has six fields (rprize1 through rprize6) which occur on up to 10 records for specific date and I am able to aggregate them successfully with the following query through VBA :-

StrSQL = "SELECT R.rfj, C.cname, R.rcourseid, SUM(R.rprize1 + R.rprize2 + R.rprize3 + R.rprize4 + R.rprize5 + R.rprize6) As Prizetot,1 " & _
              "FROM race as R, course as C " & _
              "WHERE Year(R.rdate) = 2017 " & _
              "  AND C.cid = R.rcourseid " & _
              "GROUP BY R.rfj, C.cname, R.rcourseid, Cname&rdate "

I would like to aggregate them further (ie summing each Prizetot and each 1) for as many records that occur in the year.

I have tried with :-

StrSQL = "SELECT TblMeet.rfj, TblMeet.Cname, TblMeet.rcourseid, SUM(TblMeet.Prizetot), SUM(TblMeet.Mtg) " & _
         "FROM (TblMeet " & _
         "     LEFT JOIN " & _
         "(SELECT R.rfj, C.cname, R.rcourseid, SUM(R.rprize1 + R.rprize2 + R.rprize3 + R.rprize4 + R.rprize5 + R.rprize6) As Prizetot, 1 as Mtg " & _
           "FROM race as R, course as C " & _
          "WHERE Year(R.rdate) = 2017 " & _
          "  AND C.cid = R.rcourseid " & _
          "GROUP BY R.rfj, C.cname, R.rcourseid, Cname&rdate) as Mmeet ON TblMeet.rfj = Mmeet.rfj AND TblMeet.Cname = Mmet.Cname AND TblMeet.rcourseid = Mmeet.rcourseid) "

but keep getting Syntax error in JOIN operation.

Where am I going wrong?

 

[xenou]

Where this is YOURQUERY:

SELECT 
	R.rfj, 
	C.cname, 
	R.rcourseid, 
	SUM(R.rprize1 + R.rprize2 + R.rprize3 + R.rprize4 + R.rprize5 + R.rprize6) As Prizetot,
	1 
FROM 
	race as R, course as C 
WHERE 
	Year(R.rdate) = 2017   
	AND C.cid = R.rcourseid 
GROUP BY 
	R.rfj, 
	C.cname, 
	R.rcourseid, 
	Cname&rdate

You sum it by querying the query:

SELECT
	TSUB.rfj,
	TSUB.cname,
	TSUB.rcourseid,
	SUM(TSUB.Prizetot) AS SumOfPrizetot,
	SUM(TSUB.Mtg) AS SumOfMtg
FROM
	(
		YOURQUERY
	) AS TSUB
GROUP BY
	TSUB.rfj,
	TSUB.cname,
	TSUB.rcourseid

One difference is that you will have to give a column name to the part you give the value of 1:
SUM(R.rprize1 + R.rprize2 + R.rprize3 + R.rprize4 + R.rprize5 + R.rprize6) As Prizetot,
1 AS MTG

This is awkward and unnecessary in the group by clause (possibly confusing in the results if you had more than one year involved):
Cname&rdate


I recommend you test and develop your queries without coding them as strings in VBA. That can be the last step once you know they work.

 

[ukmikeb]

Thanks xenou that is just what I needed.

As for your concerns, cname&rdate, I used that to isolate the number of meetings during a single year period.

 

[xenou]

Hi,
Doesn't your WHERE clause already isolate the results to a single year period (2017)?

 

[ukmikeb]

Hi

Maybe the wording of my response was ill-chosen.

The background is :-
I have up to 10 events in any one day at one course.
I need to aggregate the values of rprize1 through rprize6 for those 10 events as if they are a single event, ie Mtg
Each course could have up to 25 Mtgs in the year.

Hence the combination of cname &rdate to enable me to count those 'daily' events.

Please let me know if you think there is an alternative, possibly more efficient way of achieving my objective.

 

[xenou]

Up to you - if you get the right results then go for it. Generally, if a field is in the grouping clause and not the select clause, then you get additional levels of grouping that create at least some duplicates vis-a-vis the visible results. But as long as you know why that's happening and what it means then you are good.