SELECT SUM not limiting as requested

Question

I'm totally new to the land of databases. I'm wanting to get the total of more than one column from my database so am doing this... (below). The problem is, when it is returning the total for each column it isn't limiting it to the last 7 rows by date. It's returning the total for that consultant.

$result = mysql_query("SELECT SUM(NewPermJobs) AS NewPermJobsTotal, 
SUM(CandidatesSubmitted) AS CandidatesSubmittedTotal, SUM(FirstInterviewsRecorded) 
AS FirstInterviewsRecordedTotal, SUM(OldJobsReactivated) AS OldJobsReactivatedTotal, 
SUM(CandidateRecordsUpdated) AS CandidateRecordsUpdatedTotal, SUM(CompaniesAddedDream) 
AS CompaniesAddedDreamTotal, SUM(SocialContentShared) AS SocialContentSharedTotal, 
SUM(ApplicantStatusChanged) AS ApplicantStatusChangedTotal, SUM(JobsClosed) AS 
JobsClosedTotal, SUM(Revenue) AS RevenueTotal FROM dailyactivity WHERE (`Consultant` 
LIKE '%".$query."%') ORDER BY Date DESC LIMIT 0,7");?>

It's probably really messy but is the best I can put up at this stage. Any idea why it isn't limiting the sum of the columns to the last 7 ordered by date desc?

Any help would be amazing!

It may be better if you provide the table structures, sample data, and expected output. Instead of fixing your query, there maybe better approaches which could only be discussed after seeing what you are trying to achieve. — Aziz Shaikh, Aug 22 '14 at 05:34
You are aggregating without a `group by` clause, so everything is squashed in one single row. Besides that, your query is vulnerable to [SQL Injection Attacks](http://bobby-tables.com) — Barranka, Aug 22 '14 at 05:36
I'm not a PHP developer (I haven't used the language intensively), but I find comments all around about `mysql_` instructions being deprecated and recommending using `mysqli_` and/or `PDO`. For what it's worth, I forward the tip to you. — Barranka, Aug 22 '14 at 05:41
@Barranka thanks, I'm not quite sure what you mean though. Also, I'm sanitizing the search query earlier on with: `function sanitise($string) { $returnstring=str_replace("'", "\'", $string); $returnstring=str_replace("<", "", $returnstring); $returnstring=str_replace(">", "", $returnstring); if ($_SERVER['HTTP_HOST']=="localhost") { return $returnstring; } else { return mysql_real_escape_string($returnstring); //return $returnstring;` Is that incorrect? — , Aug 22 '14 at 05:41
@verrucktfuchs [Please read the "bobby-tables" site](http://bobby-tables.com). Why do you try to sanitize everything by hand, when prepared statements can do the job for you? Don't try to reinvent the wheel; use the available tools (specially if they are secure and simple to use). I work with Java a lot and, in my experience, prepared statements simplify everything. — Barranka, Aug 22 '14 at 05:45

score 0 · Accepted Answer · answered Aug 22 '14 at 05:41

You can first limit the results using a subquery and then do the sum, like this:

SELECT SUM(NewPermJobs) AS NewPermJobsTotal, 
  SUM(CandidatesSubmitted) AS CandidatesSubmittedTotal, 
  SUM(FirstInterviewsRecorded) AS FirstInterviewsRecordedTotal, 
  SUM(OldJobsReactivated) AS OldJobsReactivatedTotal, 
  SUM(CandidateRecordsUpdated) AS CandidateRecordsUpdatedTotal, 
  SUM(CompaniesAddedDream) AS CompaniesAddedDreamTotal, 
  SUM(SocialContentShared) AS SocialContentSharedTotal, 
  SUM(ApplicantStatusChanged) AS ApplicantStatusChangedTotal, 
  SUM(JobsClosed) AS JobsClosedTotal, 
  SUM(Revenue) AS RevenueTotal 
FROM (
   SELECT NewPermJobs, CandidatesSubmitted, FirstInterviewsRecorded, 
          OldJobsReactivated, CandidateRecordsUpdated, CompaniesAddedDream, 
          SocialContentShared, ApplicantStatusChanged, JobsClosed, Revenue
   FROM dailyactivity 
   WHERE (`Consultant` LIKE '%".$query."%') 
   ORDER BY Date DESC 
   LIMIT 0,7
) t

score -1 · Answer 2 · answered Aug 22 '14 at 05:37

$result = mysql_query("SELECT SUM(NewPermJobs) AS NewPermJobsTotal, 
SUM(CandidatesSubmitted) AS CandidatesSubmittedTotal, SUM(FirstInterviewsRecorded) 
AS FirstInterviewsRecordedTotal, SUM(OldJobsReactivated) AS OldJobsReactivatedTotal, 
SUM(CandidateRecordsUpdated) AS CandidateRecordsUpdatedTotal, SUM(CompaniesAddedDream) 
 AS CompaniesAddedDreamTotal, SUM(SocialContentShared) AS SocialContentSharedTotal, 
SUM(ApplicantStatusChanged) AS ApplicantStatusChangedTotal, SUM(JobsClosed) AS 
JobsClosedTotal, SUM(Revenue) AS RevenueTotal FROM dailyactivity WHERE (`Consultant` 
LIKE '%".$query."%') group by 'required_column_name' ORDER BY Date DESC LIMIT 0,7");?>

Use the group by .... It will solve your problem...

SELECT SUM not limiting as requested

2 Answers2