1

Just don't understand why i'd have to aggregrate every single table column just for SQL SERVER to look the other way. It's difficult to understand. Why should i do the following:

$this->db->select("MAX(c.id),MAX(c.name),MAX(c.phone)");
$this->db->select('MAX(state.name) as st_nm');
$this->db->select('MAX(lga.name) as "lga_nm"');
$this->db->select('MAX(cp2.point_name),MAX(cp2.location)');
$this->db->select('SUM(pr.amount) as "amt"');
$this->db->from('consultant as c');
$this->db->join('state','state.id=c.state','inner');
$this->db->join('lga','lga.id=c.lga','inner');
$this->db->join('collection_point2 as cp2','cp2.point_code=c.collection_point','inner');
$this->db->join('payment_records as pr','pr.consultant_id=c.unique_id','inner');
$this->db->where('c.agent_id',$agent);
$this->db->group_by('c.id');
$this->db->order_by('c.id');

Just to avoid this:

Msg 8120, Level 16, State 1, Line 1 Column ... is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
aknessy
  • 103
  • 1
  • 12
  • How exactly does a posi-trac rear-end on a Plymouth work? It just does. But seriously though, there's a lot of references on this. Here is a pretty solid explanation: http://stackoverflow.com/a/13998601/6167855 and here is another outside of SO http://weblogs.sqlteam.com/jeffs/archive/2007/07/20/but-why-must-that-column-be-contained-in-an-aggregate.aspx – S3S Sep 01 '16 at 18:41
  • Possible duplicate of [Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause](http://stackoverflow.com/questions/13999817/reason-for-column-is-invalid-in-the-select-list-because-it-is-not-contained-in-e) – tinonetic Sep 01 '16 at 18:43
  • @user919426 I knew there was already a similar question, i just wanted to know why it has to be so – aknessy Sep 14 '16 at 18:50
  • @aknessy [The answer](http://stackoverflow.com/a/13999903/919426) on that question gives an excellent explanation as to `why` – tinonetic Sep 15 '16 at 05:22
  • I guess it does! Still doesn't make sense... MYSQL wouldn't complain... – aknessy Sep 15 '16 at 19:28

1 Answers1

0

I know, you will always come across this situation that you will have this same question in your head. Why the heck all columns need it?

Your Options:

  1. Separate out your aggregation logic and don't throw all columns in one query. So say, let only one query returns you "c.id, SUM(pr.amount) from consultant & payments records".

  2. Use the above table to join other tables(state,lga,consultant) and no aggregation needed for any column for this query.

I recommended comparing the performance time for these query and decide based on performance.

Reasoning: It's just the way the "group by" clause is designed. Although the practical scenarios drag developers to build such absurd looking queries where aggregation is applied to every column just to make it spit data needed.

DevCod
  • 280
  • 2
  • 11