CODEWITHSUNDEEP
mysqlsql
padawan_IT
padawan_IT

Reputation: 106

Sum of different sums

Hello there community,

I have problems to combine the value of 3 different sum queries. We have a table with several columns, like: customer_id, amount_hours, start_date, end_date, one_time.

There are 3 different categories/conditions to sum them up.

  1. one_time is true

  2. end_date is NULL and one_time is false

  3. end_date is not NULL and one_time is false

I got those 3 queries done. They work perfectly. Now I want to add the results into a single column next to customer_id of course. What the table represents is the amount of "free hours" (sorry, English isn't my native language and I cannot think of a better expression). Either the customer bought a certain amount of that as a one-time payment or it has a contract that adds amount of X hours per month.

Let's say customer random-store has bought 20 hours as one-time payment, paid the whole last year for 5 hours per month and during this current year has changed it to 8 hours per month. Then it would need to sum 20 + (12months * 5 hours = 60) + (8months {as it is August now} * 8 hours = 64) = 144.

So far I got the 3 queries to get 20, 60 and 64. But if I try something like select sum ((select sum(....)+select sum(....)+select sum(....)), then it is not working. I'm getting syntax-erros and I have the feeling that I'm trying something that just isn't how the correct syntax structure should look like, adding the three different sum-queries. Tried googling it, no luck. Could someone help?

1st sum:

select sum (amount_hours) from [myTable] where customer_id=100112 AND one_time= 'True'

2nd sum:

select sum ((datediff(month, Start_Date, End_Date)+1)* amount_hours) from [myTable] where customer_id = 100112 AND one_time = 'False' and end_date is not NULL

3rd sum:

select sum ((datediff(month, Start_Date, Getdate())+1) * amount_hours) from [myTable] where customer_id = 100112 AND one_time = 'False' and end_date is NULL

My samples include a customer_id on purpose. Will do without them and a group by later.

Upvotes: 0

Views: 98

Answers (1)

Akina
Akina

Reputation: 42834

1.one_time is true

2.end_date is NULL and one_time is false

3.end_date is not NULL and one_time is false

No problems.

SELECT customer_id, 
       SUM(CASE WHEN one_time = 'True'
                THEN amount_hours 
                END) sum1,
       SUM(CASE WHEN end_date IS NULL and one_time = 'False'
                THEN (DATEDIFF(MONTH, Start_Date, End_Date) + 1) * amount_hours 
                END) sum2,
       SUM(CASE WHEN end_date IS NOT NULL and one_time = 'False'
                THEN (DATEDIFF(MONTH, Start_Date, Getdate()) + 1) * amount_hours
                END) sum3
FROM myTable
GROUP BY customer_id

You may add ELSE 0 to CASEs if needed.

Upvotes: 1

Related Questions