Netezza Error[HY000]

Question

I'm working on Netezza platform and i'm totally new.I have a query as shown below

SELECT * 
FROM  HIST_admin."$v_hist_successful_queries"
WHERE QUERY LIKE '%WI0PIR_PACKITEM_BREAKOUT_NBS%'
AND
submittime > now() - interval '6 month' 
GROUP BY QUERY

when i execute it it is giving the below mentioned error

ERROR [HY000] ERROR:  Attribute "$v_hist_successful_queries".NPSID must be GROUPed or used in an aggregate function

but i cant figure out whats the error please help. Thanks in advance.

What is the point of the GROUP BY? What are you trying to achieve? Can you provide sample data and the result you want? — Linger, Jul 24 '14 at 13:13

score 2 · Accepted Answer · answered Jul 28 '14 at 18:19

This is a generic SQL syntax issue. If you use a GROUP BY you must have some sort of aggregate function in the select list.

If I go out on a limb here and assume you are wanting as a result the distinct queries fitting that pattern from the query history database and the number of times each query was successfully run in the past six months, then your code should look something like this:

SELECT count(*) , QUERY
FROM  HIST_admin."$v_hist_successful_queries"
WHERE QUERY LIKE '%WI0PIR_PACKITEM_BREAKOUT_NBS%'
AND
submittime > now() - interval '6 month' 
GROUP BY QUERY

If you don't care about the count, rather just the existence, then you can use:

SELECT DISTINCT query
FROM  HIST_admin."$v_hist_successful_queries"
WHERE QUERY LIKE '%WI0PIR_PACKITEM_BREAKOUT_NBS%'
AND
submittime > now() - interval '6 month'

Netezza Error[HY000]

1 Answers1