CODEWITHSUNDEEP
androidsqliteandroid-sqlite
rachna
rachna

Reputation: 124

how to sum the field of two diferent table with different field using between date and group bydate in android sqlite

    public List<BillModel> Daily_Sales_Report(String from) {// passing the

date to getting the data, date wise from both the table List list = new ArrayList(); String query="select sum(Discount),sum(Del_ch),sum(Pkg_ch),substr(Created_Date,0,11) from BILLING where substr(Created_Date,0,11) like '"+from+"' group by substr(Created_Date, 0, 11)"; String query1="select count(*),sum(Amount),sum(gst_price),Created_Date from cart where Created_Date like '"+from+"' group by Created_Date";

//here i used to cursor for getting the result from two different table and setting in the model class setter.
                Cursor cursor = this.getWritableDatabase().rawQuery(query, null);
                Cursor cursor1= this.getWritableDatabase().rawQuery(query1,null);
               if (cursor.moveToFirst()&& cursor1.moveToFirst()){// cursor and cursor1 will check the data from first positon 
                    String bill_date= cursor1.getString(3  ); //getting the date from table bill
                    String cart_date= cursor.getString(  3);//getting the cart_date from cart_table
                    try {
                        do {
                            if (bill_date==cart_date){ //if cart_date from cart_table ,and bill_date from bill_table will match then it will set in the setter other wise condition will check countiue in while loop                                   BillModel model = new BillModel(); //model class
                                 model.setbill_count( cursor1.getInt( 0 ) );   // here its for count total number of item
                                model.setC_Amount( cursor1.getFloat( 1 ) );
                                model.setB_total_gst( cursor1.getFloat( 2 ) );
                                model.setB_discount( cursor.getFloat( 0 ) );
                                model.setB_del_ch( cursor.getInt( 1 ) );
                                model.setB_pack_ch( cursor.getInt( 2 ) );
                                model.setB_create_date( cursor.getString( 3) );
                               list.add( model );
                            }
                       } while (cursor.moveToNext()&& cursor1.moveToNext());// using while loop for both the cursor
                    } catch (Exception e) {
                        Log.e( "Error", String.valueOf( e ) );
                    } finally {
                        cursor.close();
                        cursor1.close();
                    }
                }
                return list;
            }

i am trying to this,What's I am doing wrong here,not getting the result in model class variable. Is it write way or not?

Upvotes: 0

Views: 81

Answers (2)

Pavan Varma
Pavan Varma

Reputation: 1239

The result you are getting is the correct response for the query you have written. The result is the effect of the left join.

Example

The resulting table of a 'left join' without 'group by' would be

select * 
from CART left join BILLING on CART.index=BILLING.index 
where substr(BILLING.Created_Date,0,11) = '02/11/2019'

enter image description here

Here there are two rows in 'CART' with index=1 and there is only one row in 'BILLING' with index=1. So when 'left join' is used, the billing row will appear twice. So when you use the 'group by' those values get added up.

Upvotes: 0

Pavan Varma
Pavan Varma

Reputation: 1239

There is an issue with the way you are saving dates. According to SQLite, your "Created_Date" field is a string.

SQLite compares strings letter by letter

Example:

According to SQLite 01/11/2019 is less than 21/10/2019 since the first date starts with '0' and the second date starts with '2'.

To fix this issue, you can use different date formats like

  • yyyy/MM/dd : 2019/11/01
  • yyyy/MM/dd HH:mm:ss : 2019/11/01 14:21:43

Upvotes: 0

Related Questions