By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,841 Members | 1,704 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,841 IT Pros & Developers. It's quick & easy.

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

P: 99
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 "CountOfadGroupId". 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
Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM adGroupHistoryMain INNER JOIN M_mainBlast ON adGroupHistoryMain.blastId = M_mainBlast.blastId
  3. WHERE (((M_mainBlast.blastStatusId)=2) AND ((M_mainBlast.constructStatusId)=3))
  4. GROUP BY adGroupHistoryMain.blastId, M_mainBlast.blastName, M_mainBlast.Created, DateDiff("d",[Created],Now());
Sep 29 '08 #1
Share this Question
Share on Google+
2 Replies


NeoPa
Expert Mod 15k+
P: 31,660
Before we start let's make your SQL legible.
Expand|Select|Wrap|Line Numbers
  1. SELECT aGHM.blastId,
  2.        MmB.blastName,
  3.        Sum(aGHM.clicks) AS SumOfclicks,
  4.        Sum(aGHM.impression) AS SumOfimpression,
  5.        Format((Sum([clicks])/Sum([impression])),'0.00%') AS CTR,
  6.        Sum(aGHM.cost) AS SumOfcost,
  7.        Count(aGHM.adGroupId) AS CountOfadGroupId,
  8.        MmB.Created,
  9.        DateDiff('d',[Created],Now()) AS Days,
  10.        Sum([cost])/DateDiff('d',[Created],Now()) AS CPD
  11. FROM adGroupHistoryMain AS aGHM INNER JOIN
  12.      M_mainBlast AS MmB
  13.   ON aGHM.blastId = MmB.blastId
  14. WHERE ((MmB.blastStatusId=2)
  15.    AND (MmB.constructStatusId=3))
  16. GROUP BY aGHM.blastId,
  17.          MmB.blastName,
  18.          MmB.Created,
  19.          DateDiff('d',[Created],Now());
Sep 29 '08 #2

NeoPa
Expert Mod 15k+
P: 31,660
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 "CountOfadGroupId". 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.
Sep 29 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.