Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: Android Sqlite CURSOR
I have database with filled records like :
 
Sr    ExpName       Date       Camt  Tamt
---   --------- -----------   ----- -----
1     Pen        10-10-2012      10    10
2     abc        10-10-2012      30    45
3     xyz        11-10-2012      15    55
4     ggg        11-10-2012      20    75
5     aaa        12-10-2012      10    85
6     nnn        13-10-2012      10    95
But along with i want to only same date records in one row and related Camt
will be added for same date Camt and output will be appears like :
DATE             AMOUNT
------------     ------
10-10-2012          40
11-10-2012          35
12-10-2012          10
13-10-2012          10
I have coded for regarding requirement using cursor and SimpleCursorAdapter but still i can't get appropriate result.
 
this output will be get in simple Sql query like :
SELECT CAST(Date AS DATE),SUM(Camt) AS AMOUNT from tb_expense GROUP BY CAST(Date AS DATE);
 
        Cursor c = db.query("tb_expense", new String[] {"Date", "SUM(Camt) AS Amount" }, null, null, "Date", null, null, null);
        adapter = new SimpleCursorAdapter(this, R.layout.myrow1, c, new String[]{"Date","Camt"},new int[]{R.id.textView3,R.id.textView5} );
        startManagingCursor(c);
        lstExpense.setAdapter(adapter);
Posted 15-Oct-12 0:34am
Edited 15-Oct-12 0:36am
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

this problem solved by myself :
Cursor c = db.query("tb_expense", new String[] 
       {DbHelper.C_Expno,"Group_Concat(ExpName) AS 
       ExpName","Date","SUM(Camt) AS Camt"}, null, null, "Date", null, null);
 
adapter = new SimpleCursorAdapter(this, R.layout.myrow1, c, new String[]
       {DbHelper.C_Expno,"ExpName","Date","Camt"},new  
       int[]{R.id.lblexpno,R.id.lblexpnm,R.id.lbldt,R.id.lblcamt} );
 
startManagingCursor(c);
lstExpense.setAdapter(adapter);
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

select * from(
select row_number() over(partition by dateadd(d,0,datediff(d,0,Date)) order by dateadd(d,0,datediff(d,0,Date)))as newSrNo, ExpName, Camt,Tamt from tbltrading where tradeid =11 group by InvoiceNo,PDate
)as q where q.SrNo=1
 

please necessary changes
hope this work
  Permalink  
Comments
Anil Dalsaniya at 15-Oct-12 6:09am
   
But how to write this code in android.........
Anil Dalsaniya at 15-Oct-12 6:11am
   
This query successfully works in sql but problem with android............
bhargavpp at 15-Oct-12 6:12am
   
please vote for that
Anil Dalsaniya at 16-Oct-12 3:46am
   
but it not works in android, how can i vote for it?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 285
1 DamithSL 265
2 CPallini 195
3 Maciej Los 175
4 George Jonsson 170
0 OriginalGriff 5,415
1 DamithSL 4,422
2 Maciej Los 3,820
3 Kornfeld Eliyahu Peter 3,470
4 Sergey Alexandrovich Kryukov 2,911


Advertise | Privacy | Mobile
Web03 | 2.8.141216.1 | Last Updated 18 Oct 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100