0

I have a long query which references many fields in different tables. I want to look up the last status for each row of data and this works in smaller queries when I group by all the other fields (excluding the date one).

With a much longer query looking at more fields, I am getting the below errors for all fields:

is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Is there something obvious I am missing please?

Thanks in advance

Uganda
  • 21
  • 3
  • 1
    Which DBMS are you using? Also: [edit] your question add the `create table` statements for the tables in question and the query you are using. [_Formatted_](http://stackoverflow.com/editing-help#code) **text** please, [no screen shots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557) –  Oct 04 '16 at 14:58
  • please show the query ,for which you are getting errors,error is obvious – TheGameiswar Oct 04 '16 at 14:58
  • 1
    Possible duplicate of [Column "invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"](http://stackoverflow.com/questions/18258704/column-invalid-in-the-select-list-because-it-is-not-contained-in-either-an-aggr) – Andrew Diamond Oct 04 '16 at 15:03

1 Answers1

0

This error simply means, that all the columns which are not part of any aggregate functions in SELECT should be part of your GROUP BY clause. From the information you shared looks like you have MAX(date_field). So all other columns should be added in group by clause. Please share your SQL script if you are still facing issues.

Justin Samuel
  • 1,063
  • 4
  • 16
  • 30
  • Thanks, I am using Microsoft SQL server 2008. My script is long sorry: – Uganda Oct 04 '16 at 15:06
  • SELECT S.client_id, S.crms_number, S.prospect_type, ST.first_source_tag, ST.last_mo_source_tag, MAX(SSH.student_status), 'Jan-16' AS Intake, 1 AS Enquired, CASE WHEN ST.last_mo_source_tag LIKE '%Jan%' AND ST.last_mo_source_tag LIKE '%16%' THEN 1 ELSE 0 END AS Offer, CASE WHEN S.student_status LIKE 'enrol%' AND S.student_status LIKE '%Jan%' AND S.student_status LIKE '%16%' THEN 1 ELSE 0 END AS Enrolled, – Uganda Oct 04 '16 at 15:07
  • Atleast your complete error message. Seems that is also missing the column name – Justin Samuel Oct 04 '16 at 15:07
  • CASE WHEN ST.first_source_tag LIKE '%[_]ff[_]%' THEN 'Fairs & Exhibitions' WHEN ST.first_source_tag LIKE '%[_]nn[_]%' OR ST.first_source_tag LIKE '%netnatives%' THEN 'Netnatives' WHEN ST.first_source_tag LIKE '%[_]fb[_]%' OR ST.first_source_tag LIKE '%facebook%' THEN 'Facebook' WHEN ST.first_source_tag LIKE '%[_]cx[_]%' OR ST.first_source_tag LIKE '%cappex%' THEN 'Cappex' WHEN ST.first_source_tag LIKE '%[_]ms[_]%' OR ST.first_source_tag LIKE '%masterstudies%' THEN 'Masterstudies' WHEN ST.first_source_tag LIKE '%[_]hc[_]%' OR – Uganda Oct 04 '16 at 15:08
  • ST.first_source_tag LIKE '%hotcourses%' THEN 'Hotcourses' WHEN ST.first_source_tag LIKE '%[_]gecko[_]%' OR ST.first_source_tag LIKE '%gecko%' OR ST.first_source_tag LIKE '%gekko%' THEN 'Gecko' WHEN ST.first_source_tag LIKE '%[_]11ten[_]%' OR ST.first_source_tag LIKE '%11ten%' THEN '11Ten' WHEN ST.first_source_tag LIKE '%[_]cl[_]%' THEN 'Clearing' WHEN ST.first_source_tag LIKE '%[_]adhoc[_]%' THEN 'Adhoc' – Uganda Oct 04 '16 at 15:08
  • WHEN CONVERT(date, ST.first_ssw_tag_date_created) = CONVERT(date, S.date_created) THEN 'Website referral' WHEN CONVERT(date, ST.first_source_tag_date_created) = CONVERT(date, S.date_created) AND ST.first_source_tag LIKE '%_bc_%' THEN 'Irregular Lists' WHEN CONVERT(date, ST.first_source_tag_date_created) = CONVERT(date, S.date_created) AND ST.first_source_tag LIKE '%emt%' THEN 'Enquiry forms' WHEN CONVERT(date, ST.first_source_tag_date_created) = CONVERT(date, S.date_created) AND ST.first_source_tag LIKE '%enq%' THEN 'Irregular Lists' – Uganda Oct 04 '16 at 15:08
  • WHEN CONVERT(date, ST.first_source_tag_date_created) = CONVERT(date, S.date_created) AND ST.first_source_tag LIKE '%OF REG%' THEN 'Webform' WHEN CONVERT(date, ST.first_general_tag_date_created) = CONVERT(date, S.date_created) AND ST.first_general_tag LIKE '%AY enquir%' THEN 'Irregular Lists' WHEN CONVERT(date, ST.first_general_tag_date_created) = CONVERT(date, S.date_created) AND ST.first_general_tag LIKE '%AY appli%' THEN 'Irregular Lists' – Uganda Oct 04 '16 at 15:08
  • WHEN ST.first_channel_mod = 'Made Offer' AND ABS(DATEDIFF(DAY, S.date_created, ST.first_mo_tag_date_created)) < 7 THEN 'Made Offer' WHEN ST.first_channel_mod = 'Prospect List' AND ST.first_source_tag LIKE '%_emt%' THEN 'Enquiry Forms' WHEN CONVERT(date, ST.first_mo_tag_date_created) = CONVERT(date, S.date_created) THEN 'Made Offer' – Uganda Oct 04 '16 at 15:08
  • WHEN ST.first_general_tag LIKE '%AY appli%' THEN 'Irregular Lists' WHEN ST.first_channel_mod = 'Prospect List' AND ST.first_source_tag LIKE '%_ff_%' THEN 'Fairs & Exhibitions' WHEN ST.first_channel_mod = 'Prospect List' AND ST.first_source_tag LIKE '%_bc_%' THEN 'Irregular Lists' WHEN ST.first_channel_mod = 'Prospect List' AND SSH.student_status = 'Applied' THEN 'AppliedList' WHEN ST.first_channel_mod = 'Prospect List' THEN 'Irregular Lists' WHEN ST.first_comm_class_mod = 'Email' THEN 'Email' WHEN ST.first_comm_class_mod – Uganda Oct 04 '16 at 15:09
  • = 'Fax' THEN 'Email' WHEN ST.first_comm_class_mod = 'Letter' THEN 'Email' WHEN ST.first_comm_class_mod = 'SMS' THEN 'Phone' WHEN ST.first_comm_class_mod = 'Web' THEN 'Enquiry Forms' WHEN ST.first_comm_class_mod = 'Unspecified' THEN 'Email' WHEN ST.first_comm_class_mod IS NULL THEN 'Email' WHEN ST.first_channel_mod = 'Prospect List' AND ST.first_mo_source_tag <> 'Unspecified' THEN 'Irregular Lists' WHEN ST.first_channel_mod = 'Made Offer' AND ST.first_mo_source_tag IS NOT NULL THEN 'Made Offer' ELSE ST.first_comm_class_mod – Uganda Oct 04 '16 at 15:09
  • END AS [Master Enquiry Source] FROM ZZ_crms_student S LEFT JOIN ZZ_crms_student_tag ST ON S.client_id = ST.client_id AND S.crms_number = ST.crms_number lEFT JOIN ZZ_crms_student_status_history SSH ON SSH.crms_number = S.crms_number AND SSH.client_id = S.client_id – Uganda Oct 04 '16 at 15:09
  • WHERE S.client_id = ‘XXX’ AND S.current_heat_status != 'Dead' AND CONVERT(DATE,S.date_created) >= '2015-02-01' AND CONVERT(DATE,S.date_created) <= '2016-01-31' AND (S.prospect_type = 'Enquiry' OR S.prospect_type = 'Prospect List') AND SSH.date_modified < '2016-02-01' GROUP BY S.crms_number, S.client_id, S. prospect_type, ST.first_source_tag, ST. last_mo_source_tag, S.student_status; – Uganda Oct 04 '16 at 15:09
  • Please copy paste your Group By also. Remember you have so many CASE statements in your select. Please add the CASE statement expressions as part of your GROUP BY instead of the column name. Reference: http://stackoverflow.com/questions/19848930/group-by-case-statement – Justin Samuel Oct 04 '16 at 15:12
  • GROUP BY S.crms_number, S.client_id, S. prospect_type, ST.first_source_tag, ST. last_mo_source_tag, S.student_status; – Uganda Oct 04 '16 at 15:25
  • How would I write that group by clause? – Uganda Oct 04 '16 at 15:25
  • Copying from the earlier URL I gave you: SELECT m.name , a.type , CASE WHEN a.result = 0 THEN 0 ELSE 1 END AS result , CURRENT_DATE - 1 AS day , count(*) AS ct FROM attempt a JOIN prod_hw_id p USING (hard_id) JOIN model m USING (model_id) WHERE ts >= '2013-11-06 00:00:00' AND ts < '2013-11-07 00:00:00' GROUP BY 1,2,3 ORDER BY 1,2,3; – Justin Samuel Oct 04 '16 at 15:36
  • Hello Justin Samuel, I have simplified the query as I want to understand how to correctly write the MAX script when needing to use groupby. Hope you can help! – Uganda Oct 06 '16 at 15:47
  • select client_id, number, status, max(date) as Latest_status_date from dbo.ZZ_status_history group by client_id, number, status – Uganda Oct 06 '16 at 15:48
  • This script shows me all statuses when I am just after the latest, i understand this is because I am grouping by client_id and number but I need to do this as I need these two to identify the case. – Uganda Oct 06 '16 at 15:49
  • Hey Uganda, just to be clear in the small query you just want grouping by client_id and number and you want the latest date? basically you dont want status? just remove them from the select and group by clause. – Justin Samuel Oct 06 '16 at 16:50
  • Hello, yes I need the status that is the most recent or last one please. Thank you – Uganda Oct 07 '16 at 23:16