I can't seem to get Group-By & Calculations to work

[AdamOnAccess]

Below is the SQL to a query that combines "Sum", "Group By" and "Count". In the case below, the field "ad group" is supposed to be counted and appear in a new field called "CountOfadGroup Id". The value it returns 170 and that is incorrect. It should be 14. When I switch the "count" to "group by", the query returns a list of the 14 rows I expected. How come I can't get the "count" to work?

Also, if you can offer any advice or links so I can learn to get queries like this to work in the future, I would really appreciate it. Specifically, I'm talking about queries that use "group-by", calculations and involve joins.

Thanks
Adam

SELECT adGroupHistoryMain.blastId, M_mainBlast.blastName, Sum(adGroupHistoryMain.clicks) AS SumOfclicks, Sum(adGroupHistoryMain.impression) AS SumOfimpression, Format((Sum([clicks])/Sum([impression])),"0.00%") AS CTR, Sum(adGroupHistoryMain.cost) AS SumOfcost, Count(adGroupHistoryMain.adGroupId) AS CountOfadGroupId, M_mainBlast.Created, DateDiff("d",[Created],Now()) AS Days, Sum([cost])/DateDiff("d",[Created],Now()) AS CPD
FROM adGroupHistoryMain INNER JOIN M_mainBlast ON adGroupHistoryMain.blastId = M_mainBlast.blastId
WHERE (((M_mainBlast.blastStatusId)=2) AND ((M_mainBlast.constructStatusId)=3))
GROUP BY adGroupHistoryMain.blastId, M_mainBlast.blastName, M_mainBlast.Created, DateDiff("d",[Created],Now());

[NeoPa]

Before we start let's make your SQL legible.

SELECT aGHM.blastId,
       MmB.blastName,
       Sum(aGHM.clicks) AS SumOfclicks,
       Sum(aGHM.impression) AS SumOfimpression,
       Format((Sum([clicks])/Sum([impression])),'0.00%') AS CTR,
       Sum(aGHM.cost) AS SumOfcost,
       Count(aGHM.adGroupId) AS CountOfadGroupId,
       MmB.Created,
       DateDiff('d',[Created],Now()) AS Days,
       Sum([cost])/DateDiff('d',[Created],Now()) AS CPD
FROM adGroupHistoryMain AS aGHM INNER JOIN
     M_mainBlast AS MmB
  ON aGHM.blastId = MmB.blastId
WHERE ((MmB.blastStatusId=2)
   AND (MmB.constructStatusId=3))
GROUP BY aGHM.blastId,
         MmB.blastName,
         MmB.Created,
         DateDiff('d',[Created],Now());

[NeoPa]

Below is the SQL to a query that combines "Sum", "Group By" and "Count". In the case below, the field "ad group" is supposed to be counted and appear in a new field called "CountOfadGroup Id". The value it returns 170 and that is incorrect. It should be 14. When I switch the "count" to "group by", the query returns a list of the 14 rows I expected. How come I can't get the "count" to work?

The first thing that springs to mind is that simply by removing the field from the GROUP BY list you have changed the specification of what the results are grouped by.

Unfortunately we have no view or understanding of your data so cannot say how much your results have been effected by this.

Also, if you can offer any advice or links so I can learn to get queries like this to work in the future, I would really appreciate it. Specifically, I'm talking about queries that use "group-by", calculations and involve joins.

Thanks
Adam

I'm sorry Adam. I have no specific links for that. Perhaps you can find something useful from Links to useful sites.