?
Solved

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

Posted on 2009-02-09
21
Medium Priority
?
967 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:wkolasa
  • 9
  • 7
  • 2
  • +2
21 Comments
 
LVL 41

Expert Comment

by:ralmada
ID: 23593939
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
 
LVL 25

Expert Comment

by:reb73
ID: 23593956
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
 
LVL 16

Expert Comment

by:sunithnair
ID: 23593957
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 25

Expert Comment

by:reb73
ID: 23593964
Actually remove the leading AND from line 16 in my previous post..
0
 
LVL 41

Expert Comment

by:ralmada
ID: 23594004
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
 

Author Comment

by:wkolasa
ID: 23594082
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
 
LVL 41

Expert Comment

by:ralmada
ID: 23594171
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
 

Author Comment

by:wkolasa
ID: 23594179
I'll give that a shot
0
 

Author Comment

by:wkolasa
ID: 23594201
The alpha case didn't work.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 23594230
Still the same error? Or is it a different one?
Can you also post your tables structure?
0
 
LVL 41

Expert Comment

by:ralmada
ID: 23594270
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
 

Author Comment

by:wkolasa
ID: 23594306
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
 
LVL 41

Expert Comment

by:ralmada
ID: 23594368
And adding r.ship_range_Method_ID  as I mentioned above?
0
 
LVL 16

Expert Comment

by:sunithnair
ID: 23594376
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
 

Author Comment

by:wkolasa
ID: 23594480
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
 
LVL 41

Expert Comment

by:ralmada
ID: 23594546
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
 

Author Comment

by:wkolasa
ID: 23594577
I received this error:

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

0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 23594595
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
 
LVL 41

Expert Comment

by:ralmada
ID: 23594639
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
 
LVL 41

Accepted Solution

by:
ralmada earned 2000 total points
ID: 23594748
and remove "from" in line 18:
...
(r.MaxOfship_range_To from >= 123 )
...
should be
(r.MaxOfship_range_To >= 123 )
 
0
 

Author Closing Comment

by:wkolasa
ID: 31546558
Thank you.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question