CODEWITHSUNDEEP
sqlpostgresql
funnelCONN
funnelCONN

Reputation: 149

Get SUM with GROUP BY and additional columns without grouping

I want to get SUM of all orders and revenue grouped by campaign_id and report_date and sku (it's PK). Next statement will work:

select sum(orders) as orders_total, sum(revenue) as  revenue_total, campaign_id, report_date 
from table
group by campaign_id, report_date

But I also want to add additional column to result: product_name. How to add this column without grouping by product_name?

Table:

CREATE TABLE schema.table (
    sku int8 NOT NULL,
    product_name text NULL,
    orders int4 NULL,
    revenue numeric(19,2) NULL,
    campaign_id int8 NOT NULL,
    report_date date NOT NULL,
    keyword text,
    CONSTRAINT campaign_brand_shelf_orders_stats_pk PRIMARY KEY (sku, campaign_id, report_date, keyword)
);

Sample data:

campaign_id     sku         product_name    keyword     orders      revenue         report_date
157836          136253279   Product1        bath        10          2500            2021-05-03
157836          136253279   Product1        cream face  1           250             2021-05-03

Expected:

campaign_id     sku         product_name      orders_total      revenue_total       report_date
157836          136253279   Product1          11                2750                2021-05-03

Upvotes: 0

Views: 110

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Well, if you have one product name per group, you can just add it to the group by:

select sum(orders) as orders_total, sum(revenue) as  revenue_total, 
       campaign_id, report_date, product_name
from table
group by campaign_id, report_date, product_name;

If there can be multiple product names and you want an arbitrary one, use an aggregation function:

select sum(orders) as orders_total, sum(revenue) as  revenue_total, 
       campaign_id, report_date, max(product_name) as product_name
from table
group by campaign_id, report_date;

If there can be multiple product names, and you want all of them, use arrays or strings:

select sum(orders) as orders_total, sum(revenue) as  revenue_total, 
       campaign_id, report_date, array_agg(distinct product_name) as product_names
from table
group by campaign_id, report_date;

Upvotes: 1

persian-theme
persian-theme

Reputation: 6638

you can use following query

SELECT campaign_id,sku,product_name,orders_total,revenue_total,report_date
FROM 
   (SELECT campaign_id,sku,product_name,
       sum(orders) OVER (partition BY campaign_id, report_date) as orders_total,
       sum(revenue) OVER (partition BY campaign_id, report_date) as revenue_total,
       report_date ,ROW_NUMBER() OVER (partition BY campaign_id) as ROWNUMBER
    FROM table) T
WHRER ROWNUMBER = 1

Upvotes: 1

Raks
Raks

Reputation: 21

You can use window function for implementing it

select sum(orders) over (partition by campaign_id, report_date ) as orders_total, 
sum(revenue) over (partition by campaign_id, report_date )  as  revenue_total, 
campaign_id, report_date ,<other columns>
from table

Upvotes: 1

Related Questions