saumitra121:

If you slightly organize your SQL, it looks rather peculiar to me:

SELECT  stat_day, obs_day, stat_week, obs_week, NULL AS stat_cputotal,
+  
        stat_memfree, stat_md5, stat_month, obs_month, stat_type,     
+  
        stat_memused, stat_10min, obs_10min, NULL AS stat_cpusystem,  
+  
        stat_epoch, obs_epoch, stat_30day, obs_30day, stat_idx, obs_id
+x,
        NULL AS stat_cpuidle, stat_memtotal, stat_qtr, obs_qtr,       
+  
        NULL AS stat_cpuuser, stat_hour, obs_hour, obs_10min, stat_10m
+in,
        obs_30day, stat_30day, obs_day, stat_day, obs_epoch, stat_epoc
+h,
        obs_hour, stat_hour, obs_idx, stat_idx, obs_month, stat_month,
+  
        obs_qtr, stat_qtr, obs_week, stat_week, serial_num,           
+  
        NULL AS dputil_cpuid, NULL AS wms_pk                          
+  
FROM ((      
    SELECT  stat_qtr, obs_qtr, stat_qtr AS cus_qtr, stat_qtr AS crash_
+qtr,
            stat_qtr AS evt_qtr, stat_30day, obs_30day, stat_30day AS 
+cus_30day,  
            stat_30day AS crash_30day, stat_30day AS evt_30day, stat_m
+onth,
            obs_month, stat_month AS cus_month, stat_month AS crash_mo
+nth,
            stat_month AS evt_month, stat_week, obs_week, stat_week AS
+ cus_week,  
            stat_week AS crash_week, stat_week AS evt_week, stat_day, 
+obs_day,
            stat_day AS cus_day, stat_day AS crash_day, stat_day AS ev
+t_day,
            stat_hour, obs_hour, stat_hour AS cus_hour, stat_hour AS c
+rash_hour,  
            stat_hour AS evt_hour, stat_10min, obs_10min, stat_10min A
+S cus_10min,
            stat_10min AS crash_10min, stat_10min AS evt_10min, stat_e
+poch,
            obs_epoch, stat_epoch AS cus_epoch, stat_epoch AS crash_ep
+och,
            stat_epoch AS evt_epoch, stat_idx, obs_idx, stat_idx AS cu
+s_idx,
            stat_idx AS crash_idx, stat_idx AS evt_idx, obs_10min, obs
+_30day,
            obs_day, obs_epoch, obs_hour, obs_idx, obs_month, obs_qtr,
+ obs_week,  
            serial_num, stat_md5, MIN(stat_memfree) AS stat_memfree,  
+  
            MIN(stat_md5) AS stat_md5, MIN(stat_type) AS stat_type,   
+  
            MIN(stat_memused) AS stat_memused, MIN(stat_memtotal) AS s
+tat_memtotal
    FROM (   
        select * from (mem_stat NATURAL LEFT OUTER JOIN host )        
+  
    )        
    GROUP BY stat_qtr, stat_30day, stat_month, stat_week, stat_day, st
+at_hour,
             stat_10min, stat_epoch, stat_idx, obs_10min, obs_30day, o
+bs_day,
             obs_epoch, obs_hour, obs_idx, obs_month, obs_qtr, obs_wee
+k,
             serial_num, stat_md5
))
[download]

In general, your use of parenthesis looks odd, but the statement that looks off is:

I'd leave the parenthesis off this one. In general, to debug something ugly like this, I like to start from the inside out and verify the sql. You don't need all the columns and such.

So first, I'd try in the sqlite3 shell:

If that works, then I'd go with:

SELECT  stat_qtr, obs_qtr, stat_qtr AS cus_qtr, stat_qtr AS crash_qtr,
        MIN(stat_memfree) AS stat_memfree
FROM (   
    <<<previous statement after it works>>> 
)
[download]

(As I said, for debugging you don't need to have all columns, at least at first.) Next, I would try:

<<<previous statement after it works>>>
GROUP BY stat_qtr
[download]

Then, piece by piece, rebuild your statement. When it fails, you know which small piece to look at. Fix that piece and continue until you've rebuilt the statement. Generally, I add most of the columns toward the end, as I find that columns are the things I rarely have trouble with.

If I had to guess, your odd use of parenthesis is the problem.

...roboticus

When your only tool is a hammer, all problems look like your thumb.

Notably:

MIN(stat_md5) AS stat_md5,
...
GROUP BY ... stat_md5
[download]

Which exactly matches the error message the original poster is getting. (It also looks like an error in another way: Just before that in the select statement is a request for stat_md5 as a separate field. So he's asking for two fields, with the same name, one of which is an aggregate of the first.)

No its giving the same error when I ran it on SQLite-3.7.11 command line. Please check my updated answer with new findings and guide me with troubleshooting this issue.

If it does not work on the sqlite3 command line then it is a problem with SQL and SQLite. There is very little we can do from a Perl perspective.

As your SQL statement is quite long, try reducing the columns and aggregates.

The error message says that you try to GROUP BY something that is an aggregate function, which SQLite does not allow (and which does not make much sense to me either). Consider moving your subselects into views maybe to disambiguate the aggregates etc.