Create, Sum and Group by "Other" Group

[dleewms]

I have a view with the following fields: cust_subtotal, last_cust_sub, branch_code, cust_id and cust_name. I need to group all records in which the cust_subtotal + last_cust_sub is less than 2500 under the name "Others" and the sum of all those records to be included in the cust_subtotal and last_cust_subtotal columns by Branch and customer_name.

Here is an example:

cust_name cust_id branch_code cust_subtotal last_cust_sub

ACME A012 PPE 250 1100
BIGGY B124 FFR 3000 1200
FRAMES F235 PPE 2400 50
NAILS N257 FFR 1500 1100
UTOPIA U975 FFR 600 900
YOYO Y864 PPE 3000 0

I would like to see

cust_name cust_id branch_code cust_subtotal last_cust_sub

FFR
BIGGY B124 FFR 3000 1200
NAILS N257 FFR 1500 1100
OTHERS Others FFR 600 900

PPE
OTHERS Others PPE 2650 1150
YOYO Y864 PPE 3000 0

Is there a way for me to select the above fields from the view and have it grouped and summed as outlined above?

Thanks!

 

[Qik3Coder]

more pseudo code:


Select CASE WHEN cust_Subtotal + last_cust_Sub > 2500 THEN Cust_name ELSE 'Others' END Cust_Name,
CASE WHEN cust_Subtotal + last_cust_Sub > 2500 THEN rtrim(Cust_ID) ELSE 'Others' END Cust_id, Branch_Code, Sum(Cust_subtotal) Cust_Subtotal, SUM(Last_cust_Sub)
FROM
....
GROUP BY CASE WHEN cust_Subtotal + last_cust_Sub > 2500 THEN Cust_name ELSE 'Others' END,
CASE WHEN cust_Subtotal + last_cust_Sub > 2500 THEN rtrim(Cust_ID) ELSE 'Others' END,
BRANCH_CODE


LOD



[blue]The doc walks in.[/blue] The good news:[green]"It's just Grumpy Old Man Syndrome."[/green] The bad news:[red]"You're not even 30."[/red]