SQL GROUP BY gives me an error

Question

I have this query:

SELECT dbo.scheduledatesff.job_no, 
               dbo.scheduledatesff.contractreceived_f, 
               dbo.scheduledatesff.designreview_f, 
               dbo.scheduledatesss.orderplotplan_s, 
               dbo.scheduledatesff.orderplotplan_f, 
               dbo.scheduledatesss.ordertrusses_s, 
               dbo.scheduledatesff.ordertrusses_f, 
               dbo.scheduledatesss.orderheatcalcs_s, 
               dbo.scheduledatesff.orderheatcalcs_f, 
               dbo.scheduledatesss.permitrelease_s, 
               dbo.scheduledatesff.permitrelease_f, 
               dbo.scheduledatesff.intselectionsappt_f, 
               dbo.scheduledatesss.excavationfile_s, 
               dbo.scheduledatesff.excavationfile_f, 
               dbo.scheduledatesss.framingfile_s, 
               dbo.scheduledatesff.framingfile_f, 
               dbo.scheduledatesss.finishingfile_s, 
               dbo.scheduledatesff.finishingfile_f, 
               dbo.scheduledatesff.startdate, 
               dbo.scheduledatesff.possession, 
               dbo.tblcustomers.expected_occupancy, 
               dbo.maxscheddate.description, 
               dbo.maxscheddate.actualfinish, 
               dbo.tblprojectmanager.pmname, 
               dbo.airport_insert.engineeringreq, 
               dbo.airport_insert.engineeringrec, 
               dbo.airport_insert.appforms, 
               dbo.airport_insert.cheque, 
               dbo.airport_insert.readytosubmit, 
               dbo.airport_insert.deficiencyreq, 
               dbo.airport_insert.deficiencyresp, 
               dbo.pmcolours.pmcolour

        FROM   dbo.scheduledatesff
               INNER JOIN dbo.tblcustomers 
                       ON dbo.scheduledatesff.job_no = dbo.tblcustomers.job_no 
               INNER JOIN dbo.scheduledatesss 
                       ON dbo.scheduledatesff.job_no = 
                          dbo.scheduledatesss.job_no 
               INNER JOIN dbo.tbljobs 
                       ON dbo.scheduledatesff.job_no = dbo.tbljobs.job_no 
               LEFT OUTER JOIN dbo.tblprojectmanager 
                            ON dbo.tbljobs.estimator = dbo.tblprojectmanager.pm 
               LEFT OUTER JOIN dbo.pmcolours 
                            ON dbo.tblprojectmanager.pm = dbo.pmcolours.pmid 
               LEFT OUTER JOIN dbo.maxscheddate 
                            ON dbo.scheduledatesff.job_no = 
                               dbo.maxscheddate.job_no 
               LEFT OUTER JOIN dbo.airport_insert 
                            ON dbo.scheduledatesff.job_no = 
                               dbo.airport_insert.job_no


         GROUP BY dbo.scheduledatesff.job_no

When I take out the group by it works fine, but with the group by I get this error:

Column 'dbo.scheduledatesff.ContractReceived_F' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Why am I getting this error and how can I fix it? What I am trying to do it group by bo.scheduledatesff.job_no, I thought it would be as simple as adding just a simple GROUP BY, how I was wrong.

All I am trying to do is group by job_no so I do not have any duplicates, with DISTINCT I still get duplicates.

Your columns not in GROUP BY must have aggregate function on them. — Mihai, Sep 22 '14 at 17:02
Think about it, if you group by some column what result would be in other columns. — Aleksandr M, Sep 22 '14 at 17:03
I just looked up aggregate function and I must say I don't understand it. — user3608800, Sep 22 '14 at 17:04
possible duplicate of [GROUP BY / aggregate function confusion in SQL](http://stackoverflow.com/questions/4611897/group-by-aggregate-function-confusion-in-sql) — GolezTrol, Sep 22 '14 at 17:07
I deleted my answer. It looks now like you want to have unique `job_no` in your result. But that means you have to do something with the other fields. Maybe you need aggregate functions, or maybe you actually needed to use `WHERE EXISTS (subquery)` instead of all those joins. Those details are not specified, and I wonder if you even know them. For instance, which `pmcolour` would you like to go with that `job_id`? — GolezTrol, Sep 22 '14 at 17:14
"group by bo.scheduledatesff.job_no" is not what you want. You want something involving GROUP BY, but not just GROUP BY. So you have not explained what you want. Explain in detail what the query result looks like in terms of the input tables. Give your table definitions including PKs, UNIQUEs, NULLs and FKs (since that tells us something about how GROUP BY acts). Read [this](https://stackoverflow.com/help/how-to-ask). — philipxy, Oct 12 '14 at 18:14

score 3 · Answer 1 · answered Sep 22 '14 at 17:03

In the select list (that is, all the columns you list out after your SELECT keyword), when you are aggregating with GROUP BY, you can only have either columns specified in the GROUP BY itself, or aggregations of columns (e.g., SUM()).

The reason for this is that any column not being grouped by and not being aggregated would have to be a single value coming from a row, but there are potentially multiple rows for each group. So which row do you use? In MySQL, it just sort of uses the first row, but that's not guaranteed. In SQL Server, you have to be explicit. You need to tell it how to get the values for columns that you are not grouping by.

What this means for you is that you need to think about what you want your results to look like. Do you want, for each job, the total count of the various order items? Then use SUM() on those columns. What that will do is sum up the values of that column for each row in the group (that is, each row that belongs to the same job_no) and the result will then be that sum, rather than a single row's value.

I suspect, however, that the GROUP BY is being included because you are getting duplicate rows due to join problems. If that's the case, please update your question so we can properly help you.

@GordonLinoff: probably because I didn't just dump a pile of code with no explanation. — siride, Sep 22 '14 at 17:43

score 0 · Answer 2 · answered Oct 10 '14 at 21:31

SELECT dbo.scheduledatesff.job_no, 
       max(dbo.scheduledatesff.contractreceived_f),
       max(dbo.tblcustomers.expected_occupancy) 
       ...
  FROM dbo.scheduledatesff
  JOIN dbo.tblcustomers 
    ON dbo.scheduledatesff.job_no = dbo.tblcustomers.job_no
  ... 
 GROUP BY dbo.scheduledatesff.job_no

SQL GROUP BY gives me an error

2 Answers2