Databases
StackOverflow2013
Postsdbo
POSQLite query running successfully in version 3.4.2 but giving error in version 3.7.11 - using aggregate functions
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQLite query running successfully in version 3.4.2 but giving error in version 3.7.11 - using aggregate functions
    primarykey
    data
    text
    <p>I have a query:</p> <pre><code>SELECT MIN(stat_md5) AS stat_md5 FROM (select * from ( mem_stat NATURAL LEFT OUTER JOIN host )) GROUP BY stat_md5; </code></pre> <p>This query is running fine in <strong>SQLite</strong> version <code>3.4.2</code>, but when I upgraded to version <code>3.7.11</code>, it is giving following error:</p> <pre><code>SQL error: aggregate functions are not allowed in the GROUP BY clause </code></pre> <p>If I remove <code>MIN()</code> or <code>GROUP BY</code> or <code>AS</code> from the query, then its working fine in version <code>3.7.11</code>. </p> <p>It means following variants of query are working fine:</p> <pre><code>SELECT MIN(stat_md5) AS stat_md5 FROM (select * from ( mem_stat NATURAL LEFT OUTER JOIN host )) SELECT MIN(stat_md5) FROM (select * from ( mem_stat NATURAL LEFT OUTER JOIN host )) GROUP BY stat_md5; SELECT stat_md5 FROM (select * from ( mem_stat NATURAL LEFT OUTER JOIN host )) GROUP BY stat_md5; </code></pre> <p>Can someone please explain why is it giving error in version 3.7.11 and workaround for it.</p> <p><strong>UPDTATE:</strong></p> <p>This is the original query. </p> <pre><code>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_idx, NULL AS stat_cpuidle, stat_memtotal, stat_qtr, obs_qtr, NULL AS stat_cpuuser, stat_hour, obs_hour, obs_10min, stat_10min, obs_30day,stat_30day, obs_day, stat_day, obs_epoch, stat_epoch, 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_month, obs_month, stat_month AS cus_month, stat_month AS crash_month, 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 evt_day, stat_hour, obs_hour, stat_hour AS cus_hour, stat_hour AS crash_hour, stat_hour AS evt_hour, stat_10min, obs_10min, stat_10min AS cus_10min, stat_10min AS crash_10min, stat_10min AS evt_10min, stat_epoch, obs_epoch, stat_epoch AS cus_epoch, stat_epoch AS crash_epoch, stat_epoch AS evt_epoch, stat_idx, obs_idx, stat_idx AS cus_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 stat_memtotal FROM (select * from ( mem_stat NATURAL LEFT OUTER JOIN host )) GROUP BY stat_qtr, stat_30day, stat_month, stat_week, stat_day, stat_hour, stat_10min, stat_epoch, stat_idx, obs_10min,obs_30day, obs_day, obs_epoch, obs_hour, obs_idx, obs_month, obs_qtr, obs_week, serial_num, stat_md5 )) </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAnonGeek
    1. USAnonGeek
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQLite query running successfully in version 3.4.2 but giving error in version 3.7.11 - using aggregate functions
      1. This table or related slice is empty.
      1. VTUpMod
    1. COi don't see why did you grouped by an already grouped field in the first place. i'd keep the MIN() and throw away the GROUP BY clause entirely, that'll should give you the same resultset
      singulars
      1. POSQLite query running successfully in version 3.4.2 but giving error in version 3.7.11 - using aggregate functions
      1. USEinacio
    2. CO@Einacio the query is little large, I just narrowed it down to the part which is giving error.
      singulars
      1. POSQLite query running successfully in version 3.4.2 but giving error in version 3.7.11 - using aggregate functions
      1. USAnonGeek
    3. CO@Einacio I have updated the question with original query. And anyways query is working fine in version `3.4.2`. I want to understand why is it not working in `3.7.11`
      singulars
      1. POSQLite query running successfully in version 3.4.2 but giving error in version 3.7.11 - using aggregate functions
      1. USAnonGeek
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload