How to Further Group Items in a Column After Group By

I have an SQL table named “DATA” as follows:

id  plan   mode         cost.     purchaseDate
1.  cd3.   ANNUALLY       34.         2020-05-04
2.  fg45.  QUARTERLY      456.        2021-01-02

I’m attempting to return the following 3 columns: plan, planCount, totalCostPerPlan

I’ve been working a query to return this and so far it looks like this:

SELECT
    plan,
    COUNT(id) as planCount,
    CASE
        WHEN mode = 'ANNUALLY' THEN SUM(cost)
        WHEN mode = 'S/ANNUALLY' THEN SUM(cost * 2)
        WHEN mode = 'QUARTERLY' THEN SUM(cost * 3)
        WHEN mode = 'MONTHLY' THEN SUM(cost * 12)
        ELSE SUM(cost)
    FROM DATA
    WHERE purchaseDate >= *not important*
    GROUP BY plan, mode
    ORDER BY plan ASC

It sort of works, however, I get the data only partially grouped as follows:

plan.   planCount.     totalCostPerPlan
cd3.        5              *not important*
cd3.        600            *not important*
cd3.        32             *not important*
fg45.       1              *not important*
fg45.       10             *not important*
h100.       7              *not important*

(The table is just an example)

How to I further group by plan? I’m trying to obtain the total value for each plan, however, they’re shown with a separate row for each mode. Please assist.

Answer

Hmmm . . . If you want to put factors into the sum() then the case is the argument to the sum():

SELECT plan, COUNT(id) as planCount,
       SUM(CASE WHEN mode = 'ANNUALLY' THEN cost
                WHEN mode = 'S/ANNUALLY' THEN cost * 2
                WHEN mode = 'QUARTERLY' THEN cost * 3
                WHEN mode = 'MONTHLY' THEN cost * 12
                ELSE cost
           END)
FROM DATA
WHERE purchaseDate >= *not important*
GROUP BY plan
ORDER BY plan ASC