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 ))
In general, your use of parenthesis looks odd, but the statement that looks off is:
select * from (mem_stat NATURAL LEFT OUTER JOIN host )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:
select * from (mem_stat NATURAL LEFT OUTER JOIN host )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>>> )
(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
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.
In reply to Re: Error in query with DBD-SQLite-1.37 - Aggregate functions are not allowed in the GROUP BY clause.
by roboticus
in thread Error in query with DBD-SQLite-1.37 - Aggregate functions are not allowed in the GROUP BY clause.
by saumitra121
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |