CODEWITHSUNDEEP
sqlpostgresql
takotsubo
takotsubo

Reputation: 746

Group by separately and together?

I have a table like this:

| counter_id | date        | sessions | campaign     | source        |
| -----------| ----------- | -------- | ------------ | ------        |
| 337        | 2022-05-01  |   25     | 12_WAVE_SALE | facebook      |
| 337        | 2022-05-01  |   100    | 12_WAVE_SALE | Not specified |
| 337        | 2022-05-01  |   75     | 12_WAVE_SALE | google        | 
| 337        | 2022-05-02  |   99     | 12_WAVE_SALE | google        | 
| 337        | 2022-05-02  |   51     | 12_WAVE_SALE | instagram     |

I expect to get this result:

| join_id                                            | date        | sessions |
| -------------------------------------------------- | ----------- | -------- |
| 337:utm_campaign=12_WAVE_SALE                      | 2022-05-01  |   100    | 
| 337:utm_campaign=12_WAVE_SALE&utm_source=facebook  | 2022-05-01  |   25     | 
| 337:utm_campaign=12_WAVE_SALE&utm_source=google    | 2022-05-01  |   75     | 
| 337:utm_campaign=12_WAVE_SALE&utm_source=google    | 2022-05-02  |   99     | 
| 337:utm_campaign=12_WAVE_SALE&utm_source=instagram | 2022-05-02  |   51     |
| 337:utm_campaign=12_WAVE_SALE                      | 2022-05-02  |   150    |
  1. Create join_id with these rules: counter_id: + if column is campaign then CONCAT utm_campaign=..., if source contains Not specified then CONCAT "" (nothing), if it contains another value then CONCAT &utm_source=...
  2. Group separately and together by campaign and source (with grouping by counter_id and date). It means that in the result set I expect data with grouping by:

I need this to create all possible combinations.

For example, with another column medium if will look like:

Now I can only create multiple "views" with grouping by these parameters separately and then use union like:

select * from .. group by campaign 

union all

select * from ... group by campaign, source

union all...

But maybe there is another better way to do this?

Upvotes: 0

Views: 65

Answers (1)

Ftisiot
Ftisiot

Reputation: 1868

Look at the CUBE option

If I recreate your case with

create table test(counter_id int, date date, sessions int, campaign text, source text);

insert into test values (337, '2022-05-01', 25, '12_WAVE_SALE', 'facebook');
insert into test values (337, '2022-05-01', 100, '12_WAVE_SALE', 'Not specified');
insert into test values (337, '2022-05-01', 75, '12_WAVE_SALE', 'google');
insert into test values (337, '2022-05-01', 99, '12_WAVE_SALE', 'google');
insert into test values (337, '2022-05-01', 51, '12_WAVE_SALE', 'instagram');

The following gives more or less what you're looking for

select counter_id, date, campaign, source, sum(sessions)
from test
group by 
    cube(counter_id, date, campaign, source)

Upvotes: 1

Related Questions