1

Sample Table

--------------------------------------------------------------------
S.No        |  Date                     |  Amount
--------------------------------------------------------------------
  1         |  20/10/2015;26/10/2015    |  5000;2000    
--------------------------------------------------------------------
  2         |  15/10/2015;25/10/2015    |  1500;6000
--------------------------------------------------------------------

How to Split Date and Amount and sum of total amount using mysql?

Example 

    select *,SPLIT_STR(date, ' ', 1) as split_date,
             SPLIT_STR(amount, ' ', 1) as split_amount , sum(split_amount) 
from  
         bill_table having str_to_date(split_date,'%d/%m/%Y') <=20/10/2015'

Actually i am expecting the ouptput:

20/10/2015 = 5000

26/10/2015 = 2000

15/10/2015 = 1500

25/10/2015 = 6000

Condition <20/10/2015 So (5000+1500) = 6500

Please help me to solve this problem.

Split Value (Array) & Adding sum of them

Dinesh G
  • 1,325
  • 4
  • 17
  • 24
  • Where does the 6500 come from? Your question is unclear what you want – Machavity Nov 17 '15 at 15:34
  • You are missing the "FROM `table`" statement in your query. Maybe add it? Also, maybe you should split on ";" instead of a whitespace? What is the actual output of this query? Also you are writing `bill_table having ...`. I think you need `bill_table WHERE ...` – Fjarlaegur Nov 17 '15 at 15:34
  • 3
    You should really rethink your tables design. A relation 1-N would solve your problem really easy and elegantly – Jorge Campos Nov 17 '15 at 15:40
  • 1
    Agreed with @JorgeCampos, or if you dont want to do that i think you should solve this in PHP. Not in MySQL. – Fjarlaegur Nov 17 '15 at 15:41
  • 1
    Possible duplicate of [SQL split values to multiple rows](http://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows) – Jorge Campos Nov 17 '15 at 15:49

1 Answers1

1

Here is a sample. You can put your Fieldname in:

SELECT
  SUBSTRING_INDEX('20/10/2015;26/10/2015', ';', 1),
  SUBSTRING_INDEX('20/10/2015;26/10/2015', ';', -1);
Bernd Buffen
  • 14,525
  • 2
  • 24
  • 39