What's wrong with my query?

[grr]

SELECT DISTINCT COUNT(*) AS occurences, post_name, post_content FROM wp_posts WHERE ((post_name LIKE '%ihop%' OR post_content LIKE '%ihop%') OR(post_name LIKE '%pancak%' OR post_content LIKE '%pancak%')) ORDER BY occurences DESC

I use wordpress and am writing a script. Here is the table:

post_name ihop
post_content pancake house

Thanks,
Ian

[arla]

My quick thought would be...

where is the group by clause?

[grr]

I thought distinct took care of that for me.

[arla]

I thought distinct took care of that for me.

Running a similar query in DB2 gives this error

SQLCODE = -122, ERROR: A SELECT STATEMENT WITH NO GROUP BY CLAUSE
CONTAINS A COLUMN NAME AND AN AGGREGATE FUNCTION IN THE SELECT CLAUSE
OR A COLUMN NAME IS CONTAINED IN THE SELECT CLAUSE BUT NOT IN THE
GROUP BY CLAUSE

It's trying to count things, but has no idea how to group the things to count, distinct just says (I only care about distinct things).

[grr]

Thanks. Would the following code work?

SELECT COUNT(*) AS occurences, post_name, post_content FROM wp_posts WHERE ((post_name LIKE '%ihop%' OR post_content LIKE '%ihop%') OR(post_name LIKE '%pancak%' OR post_content LIKE '%pancak%')) GROUP BY occurences, post_name, post_content ORDER BY occurences DESC

[rcflyer2005]

Let's see...

SELECT COUNT(*) AS occurrences, post_name, post_content 
FROM wp_posts 
WHERE post_name LIKE '%ihop%' 
OR post_content LIKE '%ihop%' 
OR post_name LIKE '%pancak%' 
OR post_content LIKE '%pancak%'
GROUP BY post_name, post_content
ORDER BY post_name, post_content ;

1. Not sure if you need DISTINCT.
2. For aggregation, you need a 'GROUP BY' before 'ORDER BY'.
3. I assume all fields come from the wp_posts table?
4. Don't think you need all the parentheses, because all conditions are 'OR'.
5 My spell checker seems to like two 'r' in occurrences.

Here is a working example form a simple vendor file:

select count(*) as occurences, postal, state 
from vendor
group by  postal, state
order by  postal, state;

See jpg image of the sql execution in zip file. No password.

Good luck.

[rcflyer2005]

OOPS... in my comment, spell checker did not catch 'form'. By bad.

[grr]

Thanks! Code now works perfectly! Here's my final query:

SELECT COUNT(*) AS occurences, post_name, post_content FROM wp_posts WHERE post_name LIKE '%ihop%' OR post_content LIKE '%ihop%' OR post_name LIKE '%pancak%' OR post_content LIKE '%pancak%'AND post_status='publish' GROUP BY post_name, post_content ORDER BY occurences DESC

`