CODEWITHSUNDEEP
sqlsql-servert-sql
Genetic1989
Genetic1989

Reputation: 634

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.

Upvotes: 0

Views: 60

Answers (3)

Gudwlk
Gudwlk

Reputation: 1157

I used your query with few modifications to retrieve the answer. In your query, Near the case statement there was an error and I corrected that by adding end and column name alias. Then I removed Order by as I want to use the result in derived table to group by further. You can use temp table or CTE (Common table Expression) to retrieve the data as well. The answer I added is using Derived table.

 DECLARE @table table(id int, [plan]   varchar(10), mode         varchar(30), cost int,     purchaseDate date)
 insert into @table 
 values
  (1,'cd3' ,  'ANNUALLY'  ,     34  ,       '2020-05-04'),
  (2,'fg45',  'QUARTERLY',      456,        '2021-01-02'),
  (3,'cd3' ,  'MONTHLY'  ,     12  ,       '2020-05-04'),
  (4,'fg45',  'QUARTERLY',      40,        '2021-01-02')


  SELECT 
   A.[plan]
 ,SUM(A.[Cost]) AS [Cost]
 FROM
 (
 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) END As [Cost]
  FROM @table
  WHERE purchaseDate >= '2020-05-04'
  GROUP BY [plan], mode   
 ) AS A

GROUP BY A.[plan]

Your final result would be something like this: and you can change the query with your tables. enter image description here

Upvotes: 0

Venkataraman R
Venkataraman R

Reputation: 12969

You can go for CROSS APPLY to calculate the new cost for each plan mode. Then, you can go for GROUP BY.

DECLARE @table table(id int, pplan   varchar(10), mode         varchar(30), cost int,     purchaseDate date)
insert into @table 
values
(1,'cd3' ,  'ANNUALLY'  ,     34  ,       '2020-05-04'),
(2,'fg45',  'QUARTERLY',      456,        '2021-01-02'),
(3,'cd3' ,  'MONTHLY'  ,     12  ,       '2020-05-04'),
(4,'fg45',  'QUARTERLY',      40,        '2021-01-02')


SELECT tt.pplan, tt.mode, count(tt.id) as countOfPlans, sum(t.newcost) as totalCost
FROM @table as tt
CROSS APPLY 
( VALUES( CASE pplan WHEN 'ANNUALLY' THEN cost 
               WHEN 'QUARTERLY' THEN cost * 3
               when 'MONTHLY' THEN cost * 12
               else cost 
               end)) as t(newcost)
group by pplan, mode
pplan mode countOfPlans totalCost
cd3 ANNUALLY 1 34
cd3 MONTHLY 1 12
fg45 QUARTERLY 2 496

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270091

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

Upvotes: 1

Related Questions