MySQL Forums
Forum List  »  General

Re: Nested aggregation for Decrementing Cost of item
Posted by: Grey Chap
Date: November 14, 2013 09:15PM

OK worked it out using Peter's Nested aggregation link as a guide.

I believe it is the right way to do this, but if not let me know.

SELECT
s.cust_id,
s.amt AS bill_Count,
ROUND(
SUM(
IF (s.amt<=100,ROUND(s.amt * .175,2),
IF((s.amt>100 AND s.amt<=200),ROUND(((s.amt - 100) * .17)+ 100 * .175,2),
IF((s.amt>200 AND s.amt<=300),ROUND(((s.amt - 200) * .165) + (100 * .17) + 100 * .175,2),
IF((s.amt>300 AND s.amt<=400),ROUND(((s.amt - 300) * .16) + (100 * .165) + (100 * .17) + 100 * .175,2),
IF((s.amt>400 AND s.amt<=500),ROUND(((s.amt - 400) * .155) + (100 * .160) + (100 * .165) + (100 * .17) + 100 * .175,2),
IF(s.amt>500,ROUND(((s.amt - 500) * .15) + (100 * .155) + (100 * .160) + (100 * .165) + (100 * .17) + 100 * .175,2),0))))))
),2 ) AS bill_Total
FROM
(
SELECT cust_id,SUM(sms_job_count) AS amt
FROM sms_job
WHERE MONTH(sms_job_date) = 10
GROUP BY cust_id
) AS s
GROUP BY s.cust_id;

The "MONTH(sms_job_Date) = 10" is to refine SELECT to month of October.

Options: ReplyQuote


Subject
Written By
Posted
Re: Nested aggregation for Decrementing Cost of item
November 14, 2013 09:15PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.