CODEWITHSUNDEEP
mysqlsql
Feign
Feign

Reputation: 270

How to sum cells in a column if a condition is met in another column in SQL

I wasn't entirely sure on how to word this question, but I'll explain my question... I have a table in a mysql database similar to 

cust_name | order_date | sales_price
----------+------------+------------
john      | 2013-02-11 | 100
matt      | 2012-02-29 | 50
sam       | 2013-03-13 | 25
tony      | 2013-02-12 | 105

I found DATE_FORMAT(order_date, '%M') can bring back the month of the date and DATE_FORMAT(order_date, '%Y') will do the same for the year.

What I could like to do is query the database to bring back something like

year  | month | tot_price
------+------------------
2013  | 02    | 205
2013  | 03    | 25
2012  | 02    | 50

Maybe even just bring back values just in 2013. I had a few attempts similar to this:

SET @july := 0;
Select customer,
    case DATE_FORMAT(order_date, '%M')
    when 'july' then @july := @july + 1
end as amount
from mytable

but, I got nowhere with it because i realized I don't know how to output a variables value... So, what should I do to write such a query? Thanks in advance!

Upvotes: 2

Views: 686

Answers (1)

juergen d
juergen d

Reputation: 204854

Select year(order_date) as order_year,
       month(order_date) as order_month,
       sum(sales_price) as tot_price,
       group_concat(cust_name) as customers_in_month
from mytable
group by order_year, order_month

SQLFiddle demo

Upvotes: 3

Related Questions