SQL subquery in query

Question

I have a table with payment:

worker_id, amount, payed, date

Table workers:

id, name, lname

I need to write SQL that will give me name, lname and sum for jun, july, august, september.

Name | Lname | Sum_JUN | Sum_JULY | Sum_AUG | Sum_SEP

I'm trying with subqueries but can't do it. Can you help me?

I created SQL (example). I will replace dates in PHP.

select w.name, w.lname,
sum(case when p.payed_date between '2014-06-01' and '2014-06-31' then p.amount else 0 end) `sum_june`,
sum(case when p.payed_date between '2014-07-01' and '2014-07-31' then p.amount else 0 end) `sum_july`,
sum(case when p.payed_date between '2014-08-01' and '2014-08-31' then p.amount else 0 end) `sum_august`,
sum(case when p.payed_date between '2014-09-01' and '2014-09-31' then p.amount else 0 end) `sum_september`,
sum(case when p.payed_date between '2014-10-01' and '2014-10-31' then p.amount else 0 end) `sum_november`
from worker w
left join worker_sum p on(w.id = p.worker_id)
group by w.id

sum for months of which year ? all or current or something else — M Khalid Junaid
– M Khalid Junaid, Commented Sep 23, 2014 at 10:17
Hello Chris. Is there any more table? Like one that contains a month column or something? — Choudhury Saadmaan Mahmid
– Choudhury Saadmaan Mahmid, Commented Sep 23, 2014 at 10:18

M Khalid Junaid · Accepted Answer · 2014-09-23 10:23:35Z

1

You can use conditional aggregation for your desired sum,But this will give you the sum for months from all years exist in your table

select w.*,
sum(case when month(p.date) = 6 then p.amount else 0 end) `sum_june`,
sum(case when month(p.date) = 7 then p.amount else 0 end) `sum_july`,
sum(case when month(p.date) = 8 then p.amount else 0 end) `sum_august`,
sum(case when month(p.date) = 9 then p.amount else 0 end) `sum_september`
from workers w
left join payment p on(w.id = p.worker_id)
group by w.id

answered Sep 23, 2014 at 10:23

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

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

1 Comment

Chris Over a year ago

I will call it from PHP so with some modifications I have what I wanted! Thanks a lot.

Collectives™ on Stack Overflow

SQL subquery in query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related