Xcode, iphone, Sqlite, need a sign function for group by?

I need an sql sign function for 开发者_StackOverflow社区my group by query to group positive and negative amounts.

Unfortunately sqlite doesn't include one.

Can anyone suggest a workaround ? or how to ad one to work with the libsqlite3.dylib framework used in xcode ?

My query is quite complicated

select fcid, sum(price), (select sum(price) from tmp b where ((b.due < a.due) 
or ((b.due = a.due) and (b.pid <= a.pid)))) as accumulated_price from tmp a 
where due >= '2011-01-25' and due < '2011-02-24' and price <> 0 group by fcid 
order by due, pid;

What I'm trying to do, is a group on sign(price) so I get two result and negative value and a positive value. These will represent total expenses and total income.

Would like to have added these tags (but I'm not allowed to create new ones libsqlite3.dylib libsqlite3)

Don't know if it's your best choice, but you can try:

select your_val > 0, sum(aggregated_value)
  from your_table
 group by your_val > 0;

This way, you should have 0 for ZERO or negative values and 1 for positive values.

UPDATE: If fcid is the field you need the sign for, you can try:

select fcid > 0, sum(price), 
       (
         select sum(price) 
           from tmp b
          where ((b.due < a.due) or ((b.due = a.due) and (b.pid <= a.pid)))
       ) as accumulated_price 
  from tmp a 
 where due >= '2011-01-25' 
   and due < '2011-02-24' 
   and price <> 0 
 group by fcid > 0;

Note that your order by clause is useless since you are grouping your results anyway.

You should just create your own sign function. See Create or Redefine SQL Functions.

Register a function using sqlite3_create_function:

sqlite3_create_function( db, "sign", 1, SQLITE_ANY, NULL, signFunc,
                         NULL, NULL);

Then implement signFunc in C.

static void signFunc(sqlite3_context *c, int argCount, sqlite3_value **args) {
    if ( argCount != 1 ) {
        sqlite3_result_null( c );
        return;
    }

    switch ( sqlite3_value_type( args[0] ) ) {
        case SQLITE_INTEGER: {
            sqlite3_int64 asInt = sqlite3_value_int64( args[0] );
            sqlite3_int64 sign = asInt < 0 ? -1 : (asInt > 0 ? 1 : 0);
            sqlite3_result_int64( c, sign );
            break;
        }
        case SQLITE_FLOAT: {
            double asDouble = sqlite3_value_double( args[0] );
            sqlite3_int64 sign = asDouble < 0 ? -1 : (asDouble > 0 ? 1 : 0);
            sqlite3_result_double( c, sign );
            break;
        }
        case SQLITE_NULL:
        default: {
            sqlite3_result_null( c );
            break;
        }
    }
}