CODEWITHSUNDEEP
sqlpostgresqlnetezza
psrpsrpsr
psrpsrpsr

Reputation: 457

How do you condense multiple groups into one?

I have a simple data set for which I want to label user_ids who have ANY matching row in a left join as having 'ever' taken an action, and by extension label those who have 'never' taken an action.

I'm having trouble condensing the values resulting from GROUP BY to get a single value for each user_id if they have 'ever' taken an action.

See rextester for sample data and query: http://rextester.com/BOVT75663

visits:

| loc_id | user_id | site_visit_date | site_visit_count |
|--------|---------|-----------------|------------------|
| 1234   | 003     | 06/05/2016      | 1                |
| 1234   | 003     | 06/06/2016      | 1                |
| 1234   | 003     | 06/09/2016      | 1                |
| 1234   | 802     | 05/18/2016      | 1                |
| 1234   | 818     | 02/19/2016      | 1                |
| 1234   | 818     | 02/21/2016      | 1                |
...and so on

actions:

| loc_id | user_id | action_date | action_category | action_count |
|--------|---------|-------------|-----------------|--------------|
| 1234   | 003     | 06/05/2016  | action123       | 2            |
| 1234   | 003     | 06/14/2016  | action123       | 5            |
| 1234   | 003     | 07/01/2016  | action123       | 1            |
| 1234   | 868     | 02/29/2016  | action123       | 13           |
| 1234   | 868     | 03/17/2016  | action123       | 9            |
| 1234   | 877     | 02/08/2016  | action123       | 5            |
| 1234   | 877     | 03/25/2016  | action123       | 4            |

DESIRED OUTPUT:

| user_id | ever_never | sum  |
|---------|------------|------|
| 3       | ever       | 7    |
| 802     | never      | NULL |
| 818     | never      | NULL |
| 868     | ever       | 22   |
| 871     | never      | NULL |
| 876     | never      | NULL |
| 877     | ever       | 9    |

CURRENT OUTPUT / QUERY:

| user_id | ever_never | sum  |
|---------|------------|------|
| 3       | ever       | 7    |
| 3       | never      | NULL |
| 802     | never      | NULL |
| 818     | never      | NULL |
| 868     | ever       | 22   |
| 868     | never      | NULL |
| 871     | never      | NULL |
| 876     | never      | NULL |
| 877     | ever       | 9    |
| 877     | never      | NULL |

with tbl as (
select
    v.loc_id
    ,v.user_id
    ,TO_CHAR(v.site_visit_date,'YYYY-MM-DD')
    ,v.site_visit_count
    ,TO_CHAR(a.action_date,'YYYY-MM-DD')
    ,a.action_category
    ,a.action_count
    ,case when a.action_count >=1 then 'ever' else 'never' end ever_never
from
    visits v
    left join actions a on v.user_id = a.user_id and v.site_visit_date = a.action_date
order by 1,2,5
)
select
    user_id
    ,ever_never
    ,sum(action_count)
from
    tbl
group by
    user_id
    ,ever_never
order by 1,2

Upvotes: 0

Views: 61

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Remove ever_never from the last group by:

select user_id, min(ever_never) as ever_never, sum(action_count)
from tbl
group by user_id
order by 1

Upvotes: 1

TheEsnSiavashi
TheEsnSiavashi

Reputation: 1255

You can use MIN function to select the ever, when there is also a never:

with tbl as (
select
   v.loc_id
   ,v.user_id
   ,TO_CHAR(v.site_visit_date,'YYYY-MM-DD')
   ,v.site_visit_count
   ,TO_CHAR(a.action_date,'YYYY-MM-DD')
   ,a.action_category
   ,a.action_count
   ,case when a.action_count >=1 then 'ever' else 'never' end ever_never
from
visits v
left join actions a on v.user_id = a.user_id and v.site_visit_date = a.action_date
order by 1,2,5
)
select
   user_id
   ,MIN(ever_never)
   ,sum(action_count)
from
   tbl
group by
   user_id
order by 1,2

Check the results: http://rextester.com/live/LQDR42614

Upvotes: 1

Related Questions