Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Query Syntax

Posted on 2016-07-26
5
Medium Priority
?
87 Views
Last Modified: 2016-07-26
I have this query:

 SELECT wp_posts.id, group_concat(wp_postmeta.post_id) as post_id, wp_postmeta.meta_value
 FROM wp_postmeta, wp_posts
 WHERE (wp_postmeta.meta_key = '_VenueState' OR wp_postmeta.meta_key = '_VenueStateProvince') AND wp_posts.post_status = 'publish' 
GROUP BY wp_postmeta.meta_value
ORDER BY wp_postmeta.meta_value ASC

Open in new window


the error I get is this: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'france-am.wp_posts.ID' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Any help I can get to rewrite this so it will run is appreciated.
0
Comment
Question by:lvollmer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 

Author Comment

by:lvollmer
ID: 41730377
0
 
LVL 41

Expert Comment

by:Sharath
ID: 41730390
 SELECT wp_posts.id, group_concat(wp_postmeta.post_id) as post_id, wp_postmeta.meta_value
 FROM wp_postmeta, wp_posts
 WHERE (wp_postmeta.meta_key = '_VenueState' OR wp_postmeta.meta_key = '_VenueStateProvince') AND wp_posts.post_status = 'publish' 
GROUP BY wp_posts.id, wp_postmeta.meta_value
ORDER BY wp_postmeta.meta_value ASC

Open in new window

0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 41730392
Or
 SELECT group_concat(wp_postmeta.post_id) as post_id, wp_postmeta.meta_value
 FROM wp_postmeta, wp_posts
 WHERE (wp_postmeta.meta_key = '_VenueState' OR wp_postmeta.meta_key = '_VenueStateProvince') AND wp_posts.post_status = 'publish' 
GROUP BY wp_postmeta.meta_value
ORDER BY wp_postmeta.meta_value ASC

Open in new window

0
 

Author Closing Comment

by:lvollmer
ID: 41730598
thanks!
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41730616
In standard SQL, a query that includes a GROUP BY clause cannot refer to nonaggregated columns in the select list that are not named in the GROUP BY clause.
see: https://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html

As a MySQL users who has (now) experienced this error please be aware that by default MySQL is NON_STANDARD. What has happened is someone has changed the server settings from that default so that the server now requires proper/standard group by queries.

What this means is:

ALL COLUMNS of the select clause need to be repeated in the group by
EXCEPT those using SUM() COUNT() AVG() ... ...  (the aggregation functions)

select col1, col2, col3, col99, sum(abc), count(xyz), avg(gel)
from ...
group by col1, col2, col3, col99

=========================================================
what you should learn to avoid is the default MySQL group by syntax
which would allow this:

select col1, col2, col3, col99, sum(abc), count(xyz), avg(gel)
from ...
group by col1
0

Featured Post

Independent Software Vendors: 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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
The purpose of this video is to demonstrate how to add AdSense Ads to a WordPress Website, and how to set up WordPress to automatically place Ads in Sidebars. This will be demonstrated using a Windows 8 PC. Log into your AdSense account. : Cli…
The purpose of this video is to demonstrate how to set up the permalinks on a WordPress Website. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Go t…

718 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