1

I am trying execute below query but I get misuse of aggregate function sum()

Query

select max(sum(entry.amount)),category.name from entry,category where entry.amount<0 and entry.cid=category.cid group by category.name

LogCat Output

android.database.sqlite.SQLiteException: misuse of aggregate function sum() (code 1): , while  compiling: select sum(entry.amount),category.name from entry,category where entry.amount<0 and  entry.cid=category.cid group by category.name 09-22 20:20:18.626: E/AndroidRuntime(4093):    at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)

Any suggestions ? regards, Brother

Brother
  • 39
  • 1
  • 2
  • 8
  • Use an **alias** for the summed field (i.e.: `... Sum(Entry.Amount) AS Total, ...`). `AS` is optional (for readability purposes only). – Phantômaxx Sep 22 '14 at 15:41
  • The sum is already aggregating the values of column entry.amount, so you cannot use another aggregation function on top of this in a single query. – personne3000 Sep 22 '14 at 15:51
  • I want maximum value given by sum aggregate function per category.Entry table has columns amount and cid which stores amount spent on particular category e.g Sports,Food,Entertainment.Cid stores the id of particular category in Category table.Category table has only two fields cid and name. It would be helpful if you post query which will help me get required result. Thank You :) – Brother Sep 22 '14 at 16:01
  • It would be helpful if you post example data and the desired result. – CL. Sep 22 '14 at 16:53

1 Answers1

2

You cannot aggregate values twice in a single query, but this can be solved with a subquery.

Try: 

select max(amountsum)
from (select sum(entry.amount) as amountsum
      from entry,category
      where entry.amount<0
        and entry.cid=category.cid
      group by category.name)

Notes :

  • Untested

  • Not sure about the logic of the query: you are aggregating values so that only one entry remains, so querying category.name does not make sense

CL.
  • 173,858
  • 17
  • 217
  • 259
personne3000
  • 1,780
  • 3
  • 16
  • 27
  • Using `max()` for a single-row subquery does not make sense. – CL. Sep 22 '14 at 16:52
  • @personne3000 Thank you for your Query.I have modified your query to get required result. Below is my modified query from which I got result I wanted to achieve. 'select max(amountsum),catid from (select sum(entry.amount)as amountsum,entry.cid as catid from entry,category where entry.amount<0 and entry.cid=category.cid group by category.cid)' Thank you :) – Brother Sep 22 '14 at 17:07
  • @CL. The subquery does not return a single row because of the group by clause. I am not sure what you actually mean. – personne3000 Sep 22 '14 at 17:16
  • Sorry, I overlooked that clause. – CL. Sep 22 '14 at 17:49