SQL - How do I sum/aggregate certain rows in a table

Question

I have a table t that looks like this (which is itself the result of a long SQL query):

 ID | Name | Active | Sales ---------------------------------------------- 1 Amy t 50 2 Bill f 4 3 Chip t 127 4 Dana t 543 5 Eve f 20

I'm looking to combine the inactive users ("Active" = f) so that I end up with a resultant table like this:

 ID | Name | Active | Sales ---------------------------------------------- 1 Amy t 50 3 Chip t 127 4 Dana t 543 0 - f 24

Is there a way to do this without repeating the initial query to get that table?

Gordon Linoff · Accepted Answer · 2018-05-16 02:42:45Z

Here is a simple way:

select id, name, active, sales from t where active union all select 0, NULL, f, sum(sales) from t where not active;

If this is the result of a complex view, you might not want to reference it twice. If so, you can use aggregation:

select (case when active then id end) as id, (case when active then name end) as name, active, sum(sales) as sales from t group by (case when active then id end), (case when active then name end) as name, active;

you're right, I don't want to reference it twice. I will try out your second code block.
"case when active then name" would be good, use the answer from @avb and mix it with this one to get the correct query.

Vao Tsun · Accepted Answer · 2018-05-15 14:07:19Z

used another table with boolean for sampling (used kvazi int, boolean and kvazi text attributes) ((different from above by having not union and just one case))(((used sum as I thought that's what you want - aggregate over a case))):

t=# select case when not rolsuper then '-' else rolname::text end , rolsuper , sum(oid::text::int) from pg_roles group by case when not rolsuper then '-' else rolname::text end,rolsuper; rolname | rolsuper | sum ----------+----------+-------- - | f | 144394 vao | t | 16555 postgres | t | 10 (3 rows)

so for you should be smth like:

select case when not active then 0 else id end , case when not active then '-' else name end , active , sum(sales) from t group by case when not active then 0 else id end , case when not active then '-' else name end , active;

apomene · Accepted Answer · 2018-05-15 13:52:10Z

SELECT ID ,Name ,Active ,Sales FROM my TABLE WHERE Active = 't' UNION SELECT 0 ,'' ,Active ,COUNT(Sales) FROM my TABLE WHERE Active = 'f' GROUP BY Active

avb · Accepted Answer · 2018-05-15 13:54:29Z

and here's without unions:

select IIF(Active = 'f', 0, ID) ID, IIF(Active = 'f', '-', Name) Name, Active, SUM(Sales) Sales from t group by IIF(Active = 'f', 0, ID), IIF(Active = 'f', '-', Name), Active

Collectives™ on Stack Overflow

SQL - How do I sum/aggregate certain rows in a table

4 Answers 4

3 Comments

Comments

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

1 Comment

Related