Most efficient way to get SUM for 2 different values in same column?-CodePudding

My table looks like this:

Color	Quarter	TotalCost
Red	Q1	100
Red	Q1	150
Red	Q1	100
Red	Q1	125
Red	Q2	100
Red	Q2	125
Red	Q2	150

The goal is to get the total cost for the current quarter (Q2) and previous quarter (Q1) for the value in the Color column.

I'm currently doing this, which works:

SELECT      Color
,           'CurrentQuarter'  = SUM(CASE WHEN Quarter = 'Q2' THEN TotalCost END)
,           'PreviousQuarter' = SUM(CASE WHEN Quarter = 'Q1' THEN TotalCost END)
FROM        #test
GROUP BY    Color

The results need to have the totals side by side:

Color	CurrentQuarter	PreviousQuarter
Red	375	475

The actual table I'm using has a few hundred thousand rows, and I have to run queries like this for different things (Color, Part, City, Owner, etc)

Is there a more efficient way to write this simple query?

CodePudding user response：

Your data

CREATE TABLE test(
   Color     VARCHAR(100) NOT NULL 
  ,Quarter   VARCHAR(100) NOT NULL
  ,TotalCost INTEGER  NOT NULL
);
INSERT INTO test
(Color,Quarter,TotalCost) VALUES 
('Red','Q1',100),
('Red','Q1',150),
('Red','Q1',100),
('Red','Q1',125),
('Red','Q2',100),
('Red','Q2',125),
('Red','Q2',150);

there exist numerous approaches to get your desired result one approach is join in subquery

SELECT t1.color,
       CurrentQuarter,
       PreviousQuarter
FROM
  (SELECT color,
          sum(TotalCost) CurrentQuarter
   FROM test
   WHERE QUARTER='Q2'   
   GROUP BY color) t1
JOIN
  (SELECT color,
          sum(TotalCost) PreviousQuarter
   FROM test
   WHERE QUARTER='Q1'
   GROUP BY color) t2 ON t1.color=t2.color

Your query requires QUARTER field

SELECT      Color
,           'CurrentQuarter'  = SUM(CASE WHEN Quarter = 'Q2' THEN TotalCost END)
,           'PreviousQuarter' = SUM(CASE WHEN Quarter = 'Q1' THEN TotalCost END)
FROM        test
GROUP BY    Color, Quarter

where it brings

Color	CurrentQuarter	PreviousQuarter
Red		475
Red	375

it requires another Subquery

SELECT Color,
            MAX(CurrentQuarter) CurrentQuarter,
            MAX(PreviousQuarter) PreviousQuarter  
            FROM
            (
SELECT      Color
,           'CurrentQuarter'  = SUM(CASE WHEN Quarter = 'Q2' THEN TotalCost END)
,           'PreviousQuarter' = SUM(CASE WHEN Quarter = 'Q1' THEN TotalCost END)
FROM        test
GROUP BY    Color, Quarter) T1
GROUP BY Color

Another approach is Pivot

SELECT * FROM   
(
SELECT      Color
,            IIF(Quarter='Q2','CurrentQuarter','PreviousQuarter') Quarter
,           SUM(TotalCost) TotalCost
FROM        test
GROUP BY    Color, Quarter
) t 
PIVOT(
    SUM(TotalCost) 
    FOR Quarter IN (
        [CurrentQuarter], 
        [PreviousQuarter])
) AS pivot_table;

DBFIDDLE

CodePudding user response：

You could use windowing functions -- depending on many things that may or may not be faster -- with the windowing function you don't need to use the group by and you can only sum elements of various windows (prior elements based on ordering, last 3 elements etc)

Here is the default case that will give you the same numbers as your prior query (but with the value for the quarter of the row on each row.)

  SELECT  *,
          SUM(TotalCost) OVER (PARTITION BY Quarter) AS QuarterTotalCost
  FROM        #test