CODEWITHSUNDEEP
sqloracle-database
Heisenberg
Heisenberg

Reputation: 5299

How to group complicated condition in sql

I'd like to group by region where there are customerswho has type=a

region customer type score
A       a        a     1
A       b        b     2
A       c        a     3 
B       d        c     4 
B       e        d     5 
C       f        a     6 
C       g        c     7

Therefore after first step

region customer type score
A       a        a     1
A       b        b     2
A       c        a     3 
C       f        a     6 
C       g        c     7

And then I groupby in region

region  sum(score)
A         6
C        13

also I'd like to extract customer whose type=a

region customer type
A         a      a
A         c      a 
C         f      a

Then I'd like to merge above.

My desired result is like following

customer sum_in_region
a           6
c           6
f          13

Are there any way to achieve this?

My work is till the second step.. How can I proceed further?

SELECT t1.region,t1.customer, t1.type, t1.score
  FROM yourTable t1
 WHERE EXISTS (SELECT 1
                 FROM yourTable t2
                WHERE t2.region = t1.region
                  AND t2.type = 'a');

Thanks

Upvotes: 0

Views: 43

Answers (3)

sticky bit
sticky bit

Reputation: 37477

Join the table to a derived table that does your first two steps.

SELECT t3.customer,
       x1.score
       FROM yourtable t3
            INNER JOIN (SELECT t1.region,
                               sum(score) score
                               FROM yourtable t1
                                    WHERE EXISTS (SELECT *
                                                         FROM yourtable t2
                                                         WHERE t2.region = t1.region
                                                               AND t2.type = 'a')
                               GROUP BY t1.region) x1
                       ON x1.region = t3.region
       WHERE t2.type = 'a';

Upvotes: 2

Atif
Atif

Reputation: 2210

You can use below query:

SQLFiddle

with country_tmp as
(SELECT t1.region,t1.customer, t1.type, t1.score
  FROM country t1
 WHERE EXISTS (SELECT 1
                 FROM country t2
                WHERE t2.region = t1.region
                  AND t2.type = 'a'))
                  select y.customer, x.score from
(select a.region, sum(a.score) score from (
SELECT t1.region,t1.customer, t1.type, t1.score
  FROM country_tmp t1) a
group by region) x , (SELECT t1.region,t1.customer, t1.type
  FROM country_tmp t1
 Where t1.type = 'a') y where x.region = y.region;

Upvotes: 1

sammywemmy
sammywemmy

Reputation: 28709

You could use the windows functions to get your result; the first step filters for only rows where type is a, based on the region. The second step then gets the sum of scores, based again on the region, before selecting only customer and sum columns :

with filter_type_a as 
(select region, customer, type, score 
from
(select *,
sum(type=="a") over (partition by region) as counter
from your_table)
where counter > 0)

select customer, sum_region
from
(select customer, type,
sum(score) over (partition by region) as sum_region
from filter_type_a)
where type=="a";

Upvotes: 1

Related Questions