CODEWITHSUNDEEP
sqlpostgresqlamazon-redshift
apz_fed
apz_fed

Reputation: 25

Selecting a total without using a subquery when segmenting

I would like to do something like this, without using a subquery to have the total:

    select
    category,
    date,
    sum(qty)/(select sum(qty) from table where t.date= table.date) as share_of_total
    from table t
    group by category,date;

The table looks like this:

category    date    qty
1   2016-11-01  3
1   2016-12-01  4
2   2016-12-01  6
2   2016-11-01  7

The expected result would be a table like this: 

category date share_of_total
1 2016-11-01 30% 
2 2016-11-01 70% 
1 2016-12-01 40% 
2 2016-12-01 60%

Upvotes: 0

Views: 47

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51579

UPDATED

This is the answer:

b=# create table "table" (category int,"date" date,qty int);
CREATE TABLE
b=# insert into "table" values (1,'2016-11-01',3), (1,'2016-12-01',4), (2,'2016-12-01',6), (2,'2016-11-01',7);
INSERT 0 4
b=# select 
  category
, "date"
, concat((qty*100) / sum(qty) over (partition by "date") ,'%') share_of_total from "table" 
order by "date", category
;
 category |    date    | share_of_total
----------+------------+----------------
        1 | 2016-11-01 | 30%
        2 | 2016-11-01 | 70%
        1 | 2016-12-01 | 40%
        2 | 2016-12-01 | 60%
(4 rows)

And example how you should ask your question. Including schema creation into question saves time on typing to reproduce what you have. People might not want to spend their time to help you, just because you did not spent yours to provide a sand box.

Upvotes: 1

Related Questions