Adding different rows in SQL based on column value as new row and new column value

Question

I'm entirely willing to admit that I am going about this incorrectly, but here's the situation. I was given a table like this:

qtr    cust_type      cust_cnt           

QTR1   new_cust       100
QTR1   return_cust    50 
QTR2   new_cust       150  
QTR2   return_cust    75

and I am trying to create a new cust_type row for each qtr that shows the combined amounts of the other cust_types, like this as an end result:

qtr    cust_type      cust_cnt           

QTR1   new_cust       100
QTR1   return_cust    60 
QTR1   total_cust     160
QTR2   new_cust       150  
QTR2   return_cust    75
QTR2   total_cust     225

Why on earth would I do this? The reason is not great- Tableau is very picky about how information is input in order to form specific graph types.

Is there some SQL syntax that can accomplish the second graph? I cannot seem to figure one out. I'm also very new to SQL :D

I think Tableau should be able to add those summary rows. Either way the union approach should be good too. — shawnt00
– shawnt00, Commented Aug 24, 2018 at 16:37
Why the 50 in the first quarter for returning customers becomes 60? — sticky bit
– sticky bit, Commented Aug 25, 2018 at 2:17

Lukasz Szozda · Accepted Answer · 2018-08-24 16:32:00Z

1

You could use:

SELECT qtr, cust_type, cust_cnt   
FROM tab
UNION ALL
SELECT qtr, 'total_cust' AS cust_type, SUM(cust_cnt) AS cust_cnt
FROM tab
GROUP BY qtr
ORDER BY qtr, cust_cnt;

answered Aug 24, 2018 at 16:32

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ian Over a year ago

Thanks, this is the logical structure I needed.

Bruce Zhao · Accepted Answer · 2018-08-24 23:58:05Z

0

I encountered the same when visualize the data in Tableau. It supports total if you present data in a grid but sometimes we need show the total in a dropdown list. Unfortunately, Redshift doesn't support cube or rollup. I did use the same way what Lukasz suggested.

answered Aug 24, 2018 at 23:58

Bruce Zhao

311 silver badge2 bronze badges

1 Comment

Ian Over a year ago

Definitely some things to have to get used to based on the database to visualization combo!

Collectives™ on Stack Overflow

Adding different rows in SQL based on column value as new row and new column value

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related