Mysql query for sum based off a value in a column group by that column

Question

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!

This is a basic aggregation query with a where clause. Are you familiar with SQL? — Gordon Linoff
– Gordon Linoff, Commented Jun 27, 2014 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
– didierc, Commented Jun 27, 2014 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
– willchye, Commented Jun 27, 2014 at 20:49
Yes, sorry I only spotted the last bit, I thought you only wrote a fragment of it. — didierc
– didierc, Commented Jun 27, 2014 at 20:59

Siyual · Accepted Answer · 2014-06-27 20:54:32Z

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

answered Jun 27, 2014 at 20:54

Siyual

17k8 gold badges47 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

didierc · Accepted Answer · 2014-06-27 20:54:45Z

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.

answered Jun 27, 2014 at 20:54

didierc

14.8k3 gold badges35 silver badges52 bronze badges

2 Comments

willchye Over a year ago

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..

didierc Over a year ago

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.

Collectives™ on Stack Overflow

Mysql query for sum based off a value in a column group by that column

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related