0

I am a mysql newbie here need some help with a sql query I cant quite figure out.

I currently have a table Transact that contains something like this:

Paytype Amount  Date
Cash    100.0   2013-08-01
Visa    50.00   2013-09-2
Check   50.00   2013-11-12
Cash    75.00   2013-12-01
Cash    50.00   2013-12-19
Visa    100.00  2014-3-01
Cash    20.00   2014-5-20

Amount and Pay type ordered based on transaction dates, I want it to be queried out into a table that gives sum of the amount for each paytype and grouped by paytype like this:

Paytype Total
Cash    245.00
Visa    100.00
Check   50.00

Having it based off a date range so say I want that based by 2013-12-15 to 2014-5-01. Currently I can just get out one single sum for a specific paytype with 

select paytype, (SUM(IF(paytype = 'Cash', Amount, 0)) from Transact group by paytype.

Please help with the proper query to execute that! Thanks much!

didierc
  • 14,572
  • 3
  • 32
  • 52
willchye
  • 43
  • 13
  • This is a basic aggregation query with a `where` clause. Are you familiar with SQL? – Gordon Linoff Jun 27 '14 at 20:40
  • You should put your query in the question, and let people here figure out where it's incorrect. Otherwise people will assume you don't know and just want to have it done by someone else. – didierc Jun 27 '14 at 20:45
  • Hi thanks for the response, I did put my query up there. I am only know the very basic SQL queries so. I tried select paytype, SUM(paytype = 'Cash') as total from Transact group by paytype. which only gives me the sum of cash. I guess I want something like: select payment, amount as total where amount = SUM(payment = 'SD') and SUM(payment='C') group by payment; – willchye Jun 27 '14 at 20:49
  • http://stackoverflow.com/questions/18603318/mysql-query-for-certain-date-range – didierc Jun 27 '14 at 20:52
  • Yes, sorry I only spotted the last bit, I thought you only wrote a fragment of it. – didierc Jun 27 '14 at 20:59

2 Answers2

1

You can do this with a basic Group By and Sum query:

Select  Paytype, Sum(Amount) As Total
From    Transact
Where   Date Between '2013-12-15' And '2014-05-01'
Group by Paytype
Siyual
  • 16,415
  • 8
  • 44
  • 58
1

You want to sum amounts per payment type in a date interval. This translates pretty straightforwardly into sql (I think):

Select Paytype, sum(Amount) as Total
From Transact
Where (`Date` between ?start_date
  And  ?end_date)
Group by Paytype

Just replace ?start_date and ?end_date with the range you want to check.

didierc
  • 14,572
  • 3
  • 32
  • 52
  • Thanks! Yeah that is actually very simple and straightforward, I over complicate it. Because if its just a regular sum(amount), it will sum the entire column right? so I thought that wouldn't work, but I guess that's where the group by comes in to do the work to separate each based on the different values in Paytype.. – willchye Jun 27 '14 at 21:03
  • Things are simple when you formulate them the right way, and it's something you learn to do with practice. I am learning every day as well. – didierc Jun 27 '14 at 21:05