SQL Error - not contained in either an aggregate function or the GROUP BY clause.

Hi, I'm receiving the following error from MS SQL / Coldfusion debugger:  

[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC SQL Server Driver][SQL Server]Column 'tbl_shipmethcntry_rel.shpmet_cntry_Country_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.   I've pasted my sql statement below.  

Any help would be greatly appreciated.
SELECT 
							Min(r.ship_range_From) AS MinOfship_range_From,
							Max(r.ship_range_To) AS MaxOfship_range_To, 
							m.shipmeth_ID,
							m.shipmeth_Name, 
							c.shpmet_cntry_Country_ID, 
							m.shipmeth_Sort,
							m.shipmeth_Archive
						FROM 
							(tbl_shipmethcntry_rel c
							INNER JOIN tbl_shipmethod m
							ON c.shpmet_cntry_Meth_ID = m.shipmeth_ID) 
							INNER JOIN tbl_shipranges r
							ON m.shipmeth_ID = r.ship_range_Method_ID 
						GROUP BY 
							m.shipmeth_ID,
							m.shipmeth_Name, 
							c.shpmet_cntry_Country_ID, 
							m.shipmeth_Sort,
							m.shipmeth_archive
						HAVING 
							(Min(r.ship_range_From) <= <cfqueryparam cfsqltype="cf_sql_float" value="#rangeValue#" />)
							AND (Max(r.ship_range_To) >= <cfqueryparam cfsqltype="cf_sql_float" value="#rangeValue#" />)
							AND (c.shpmet_cntry_Country_ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#Arguments.ShipToCountryID#" />)
							AND (m.shipmeth_archive = 0)
						ORDER BY 
							m.shipmeth_Sort

Open in new window

wkolasaAsked:
Who is Participating?
 
ralmadaConnect With a Mentor Commented:
and remove "from" in line 18:
...
(r.MaxOfship_range_To from >= 123 )
...
should be
(r.MaxOfship_range_To >= 123 )
 
0
 
ralmadaCommented:
First, remove parentesis here
...(tbl_shipmethcntry_rel c
INNER JOIN tbl_shipmethod m
ON c.shpmet_cntry_Meth_ID = m.shipmeth_ID)  ...
Secondly in your having clause you can only put aggregate fields. So try something like this:

SELECT 
							Min(r.ship_range_From) AS MinOfship_range_From,
							Max(r.ship_range_To) AS MaxOfship_range_To, 
							m.shipmeth_ID,
							m.shipmeth_Name, 
							c.shpmet_cntry_Country_ID, 
							m.shipmeth_Sort,
							m.shipmeth_Archive
						FROM 
							tbl_shipmethcntry_rel c
							INNER JOIN tbl_shipmethod m
							ON c.shpmet_cntry_Meth_ID = m.shipmeth_ID 
							INNER JOIN tbl_shipranges r
							ON m.shipmeth_ID = r.ship_range_Method_ID 
WHERE (c.shpmet_cntry_Country_ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#Arguments.ShipToCountryID#" />)	AND (m.shipmeth_archive = 0)
 
						GROUP BY 
							m.shipmeth_ID,
							m.shipmeth_Name, 
							c.shpmet_cntry_Country_ID, 
							m.shipmeth_Sort,
							m.shipmeth_archive
						HAVING 
							(Min(r.ship_range_From) <= <cfqueryparam cfsqltype="cf_sql_float" value="#rangeValue#" />)
							AND (Max(r.ship_range_To) >= <cfqueryparam cfsqltype="cf_sql_float" value="#rangeValue#" />)													ORDER BY 
							m.shipmeth_Sort

Open in new window

0
 
reb73Commented:
Here you go -


SELECT 
        Min(r.ship_range_From) AS MinOfship_range_From,
        Max(r.ship_range_To) AS MaxOfship_range_To, 
        m.shipmeth_ID,
        m.shipmeth_Name, 
        c.shpmet_cntry_Country_ID, 
        m.shipmeth_Sort,
        m.shipmeth_Archive
FROM 
        (tbl_shipmethcntry_rel c
        INNER JOIN tbl_shipmethod m
        ON c.shpmet_cntry_Meth_ID = m.shipmeth_ID) 
        INNER JOIN tbl_shipranges r
        ON m.shipmeth_ID = r.ship_range_Method_ID 
WHERE
        AND (c.shpmet_cntry_Country_ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#Arguments.ShipToCountryID#" />)
        AND (m.shipmeth_archive = 0)
GROUP BY 
        m.shipmeth_ID,
        m.shipmeth_Name, 
        c.shpmet_cntry_Country_ID, 
        m.shipmeth_Sort,
        m.shipmeth_archive
HAVING 
        (Min(r.ship_range_From) <= <cfqueryparam cfsqltype="cf_sql_float" value="#rangeValue#" />)
        AND (Max(r.ship_range_To) >= <cfqueryparam cfsqltype="cf_sql_float" value="#rangeValue#" />)
ORDER BY 
        m.shipmeth_Sort

Open in new window

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
sunithnairCommented:
If you are using group by you can only select the columns that exists in the group by i.e. you cannot use Min(r.ship_range_From) AS MinOfship_range_From and                                                    Max(r.ship_range_To) AS MaxOfship_range_To

unless you add them also into the group by
0
 
reb73Commented:
Actually remove the leading AND from line 16 in my previous post..
0
 
ralmadaCommented:
Same with better indentation
SELECT 
 
	Min(r.ship_range_From) AS MinOfship_range_From,
	Max(r.ship_range_To) AS MaxOfship_range_To, 
	m.shipmeth_ID,
	m.shipmeth_Name, 
	c.shpmet_cntry_Country_ID, 
	m.shipmeth_Sort,
	m.shipmeth_Archive
FROM 
tbl_shipmethcntry_rel c
INNER JOIN tbl_shipmethod m
ON c.shpmet_cntry_Meth_ID = m.shipmeth_ID 
INNER JOIN tbl_shipranges r
ON m.shipmeth_ID = r.ship_range_Method_ID 
WHERE (c.shpmet_cntry_Country_ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#Arguments.ShipToCountryID#" />)	AND (m.shipmeth_archive = 0)
GROUP BY 
	m.shipmeth_ID,
	m.shipmeth_Name, 
	c.shpmet_cntry_Country_ID, 
	m.shipmeth_Sort,
	m.shipmeth_archive
HAVING 
(Min(r.ship_range_From) <= <cfqueryparam cfsqltype="cf_sql_float" value="#rangeValue#" />)
AND (Max(r.ship_range_To) >= <cfqueryparam cfsqltype="cf_sql_float" value="#rangeValue#" />)	
ORDER BY m.shipmeth_Sort 

Open in new window

0
 
wkolasaAuthor Commented:
I've tried both solutions... still receiving the same error.  I'm attaching the SQL post values below
SELECT Min(r.ship_range_From) AS MinOfship_range_From, Max(r.ship_range_To) AS MaxOfship_range_To, m.shipmeth_ID, m.shipmeth_Name, c.shpmet_cntry_Country_ID, m.shipmeth_Sort, m.shipmeth_Archive FROM tbl_shipmethcntry_rel c INNER JOIN tbl_shipmethod m ON c.shpmet_cntry_Meth_ID = m.shipmeth_ID INNER JOIN tbl_shipranges r ON m.shipmeth_ID = r.ship_range_Method_ID WHERE (c.shpmet_cntry_Country_ID = (param 1) ) AND (m.shipmeth_archive = 0) GROUP BY m.shipmeth_ID, m.shipmeth_Name, c.shpmet_cntry_Country_ID, m.shipmeth_Sort, m.shipmeth_archive HAVING (Min(r.ship_range_From) <= (param 2) ) AND (Max(r.ship_range_To) >= (param 3) ) ORDER BY m.shipmeth_Sort  
VENDORERRORCODE   8120 

Open in new window

0
 
ralmadaCommented:
Is your db case sensitive?
If so in your group by you have m.shipmeth_archive but in your select you have m.shipmeth_Archive. They are not the same
 
0
 
wkolasaAuthor Commented:
I'll give that a shot
0
 
wkolasaAuthor Commented:
The alpha case didn't work.
0
 
ralmadaCommented:
Still the same error? Or is it a different one?
Can you also post your tables structure?
0
 
ralmadaCommented:
Also try adding this field r.ship_range_Method_ID to your group by
...GROUP BY
 m.shipmeth_ID,
 m.shipmeth_Name,
 c.shpmet_cntry_Country_ID,
 m.shipmeth_Sort,
 m.shipmeth_archive,
 r.ship_range_Method_ID  ...
0
 
wkolasaAuthor Commented:
Yes, same error.  Here are the table structures:

tbl_shipranges:  
ship_range_ID  ;  ship_range_method_ID  ;  ship_range_From  ;  ship_range_To  ;  ship_range_Amount

tbl_shipmethcntry_rel:
shpmet_cntry_ID  ;  shpmet_cntry_Meth_ID  ;  shpmet_cntry_Country_ID

tbl_shipmethod
shipmeth_ID  ;  shipmeth_Name  ;  shipmeth_Rate  ;  shipmeth_Sort  ;  shipmeth_Archive
0
 
ralmadaCommented:
And adding r.ship_range_Method_ID  as I mentioned above?
0
 
sunithnairCommented:
Try this one, I am not sure it this is what you want
SELECT 
	Min(r.ship_range_From) AS MinOfship_range_From,
	Max(r.ship_range_To) AS MaxOfship_range_To, 
	m.shipmeth_ID,
	m.shipmeth_Name, 
	c.shpmet_cntry_Country_ID, 
	m.shipmeth_Sort,
	m.shipmeth_Archive
FROM 
	(tbl_shipmethcntry_rel c
	INNER JOIN tbl_shipmethod m
	ON c.shpmet_cntry_Meth_ID = m.shipmeth_ID) 
	INNER JOIN tbl_shipranges r
	ON m.shipmeth_ID = r.ship_range_Method_ID 
GROUP BY 
	Min(r.ship_range_From),
	Max(r.ship_range_To),
	m.shipmeth_ID,
	m.shipmeth_Name, 
	c.shpmet_cntry_Country_ID, 
	m.shipmeth_Sort,
	m.shipmeth_archive
HAVING 
	(Min(r.ship_range_From) <= <cfqueryparam cfsqltype="cf_sql_float" value="#rangeValue#" />)
	AND (Max(r.ship_range_To) >= <cfqueryparam cfsqltype="cf_sql_float" value="#rangeValue#" />)
	AND (c.shpmet_cntry_Country_ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#Arguments.ShipToCountryID#" />)
	AND (m.shipmeth_archive = 0)
ORDER BY 
	m.shipmeth_Sort

Open in new window

0
 
wkolasaAuthor Commented:
Unfortunately the error is still occuring (I did try all posted solutions so far):

[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC SQL Server Driver][SQL Server]Column 'tbl_shipmethcntry_rel.shpmet_cntry_Country_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
 

HAVING
(Min(r.ship_range_From) <= <cfqueryparam cfsqltype="cf_sql_float" value="#rangeValue#" />)
AND (Max(r.ship_range_To) >= <cfqueryparam cfsqltype="cf_sql_float" value="#rangeValue#" />)    
ORDER BY m.shipmeth_Sort
</cfquery>
0
 
ralmadaCommented:
And what about this?
SELECT 
	r.MinOfship_range_From, 
	r.MaxOfship_range_To, 
	m.shipmeth_ID, 
	m.shipmeth_Name, 
	c.shpmet_cntry_Country_ID, 
	m.shipmeth_Sort, 
	m.shipmeth_Archive 
FROM tbl_shipmethcntry_rel c 
INNER JOIN 
(select distinct shipmeth_ID, shipmeth_Name, shipmeth_Sort, shipmeth_Archive from tbl_shipmethod) m ON c.shpmet_cntry_Meth_ID = m.shipmeth_ID 
INNER JOIN 
(select ship_range_Method_ID, Min(ship_range_From) AS MinOfship_range_From, Max(ship_range_To) AS MaxOfship_range_To from tbl_shipranges group by ship_range_Method_ID) r ON m.shipmeth_ID = r.ship_range_Method_ID
WHERE 
	(c.shpmet_cntry_Country_ID = (param 1) ) AND 
	(m.shipmeth_archive = 0) and 
	(r.MinOfship_range_From <= (param 2) ) AND 
	(r.MaxOfship_range_To from >= (param 3) )
ORDER BY m.shipmeth_Sort 

Open in new window

0
 
wkolasaAuthor Commented:
I received this error:

Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '1'.

0
 
Scott PletcherSenior DBACommented:
You need to move the last two conditions in your original query from the HAVING clause to a WHERE clause:

SELECT ...
FROM ...
WHERE
    (c.shpmet_cntry_Country_ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#Arguments.ShipToCountryID#" />) AND
    (m.shipmeth_archive = 0)
HAVING ...first two conditions same as before...
ORDER BY ...
0
 
ralmadaCommented:
you need to change param 1 2 and 3 with the value.
c.shpmet_cntry_Country_ID = (param 1) )... should be  c.shpmet_cntry_Country_ID = 123 --whatever your country code or @yourparameter
0
 
wkolasaAuthor Commented:
Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.