Database
Enthusiast

## Group by, distinct, PARTITION BY

Hi everybody,

At first I want to say this forum helps me really a lot, but in this case I can't find a solution that helps me out.

I try to have a summery of a 2 tables I've joined.

`SELECT distinct a.par_nbr,count(a.shp_nbr) OVER (PARTITION BY a.par_nbr),sum(case when b.wgt_typ_cd  = 'L' then cast((b.tot_wgt*0.453592) AS DECIMAL(6,2)) else  b.tot_wgtend) OVER (PARTITION BY a.shp_nbr) as Wgtfrom event_history ainner visibility bon a.shp_nbr = b.shp_nbrwhere a.evnt_dt between '2016-03-12' and '2016-03-18'and a.par_cons_nbr eq any ('A')`

That's the result

par_nbr Group Count(shp_nbr)    Wgt

216621739627         5                1,36

216621739627         5                2,36

216621739627         5                2,45

216621739627         5                2,49

216621739627         5                3,08

What I want is

par_nbr Group Count(shp_nbr)    Wgt

216621739627         5                11,74

Is there any solution I'm not thing about?

Thank you very much for every hind you can give.

Regards

6 REPLIES
Senior Apprentice

## Re: Group by, distinct, PARTITION BY

What's the relationship between shp_nbr and par_nbr?

Are there multiple par_nbr per shp_nbr or 1:n or m:n?

Enthusiast

## Re: Group by, distinct, PARTITION BY

Hi Dieter,

there are many shp_nbr to realated to one par_nbr.

Regards

Senior Apprentice

## Re: Group by, distinct, PARTITION BY

Well, seems like you don't need an OLAP-function but good old GROUP BY in that case:

`SELECT   a.par_nbr,   COUNT(a.shp_nbr),   SUM(CASE          WHEN b.wgt_typ_cd  = 'L'          THEN CAST((b.tot_wgt*0.453592) AS DECIMAL(6,2))          ELSE b.tot_wgt       END) AS WgtFROM event_history a JOIN visibility b  ON a.shp_nbr = b.shp_nbrWHERE a.evnt_dt BETWEEN '2016-03-12' AND '2016-03-18'  AND a.par_cons_nbr EQ ANY ('A')GROUP BY a.par_nbr`
Enthusiast

## Re: Group by, distinct, PARTITION BY

Thank you so much Dieter.

I tried it before only with grouping but it failed.

however it works now.

Thanks a million.

Regards Sven

Enthusiast

## Re: Group by, distinct, PARTITION BY

Sorry, I have to come back in this case.

I recognize that it could be that the same shp_nbr appear more than one time with the same data.

But I need it a only one time. I tried it with distinct the shp_nbr but for the weight it calculates more than once.

e.g.

shp_nbr    Wgt

AAA1        5

AAA1        5

AAA2        8

AAA3        2

AAA2        8

my result with the query is

shp_nbr    Wgt

AAA1        10

AAA2        16

AAA3        2

it should be

shp_nbr    Wgt

AAA1        5

AAA2        8

AAA3        2

that at the end i Have

par_nbr    Wgt

A               15

Hope not explained to complicated.

Regards Sven

Senior Apprentice

## Re: Group by, distinct, PARTITION BY

Hi Sven,

then you need a Derived Table as DISTINCT is processed after GROUP BY:

`SELECT   par_nbr,   COUNT(*),   SUM(Wgt)FROM  (   SELECT DISTINCT       a.par_nbr,      a.shp_nbr,      CASE          WHEN b.wgt_typ_cd  = 'L'          THEN CAST((b.tot_wgt*0.453592) AS DECIMAL(6,2))          ELSE b.tot_wgt       END AS Wgt   FROM event_history a    JOIN visibility b     ON a.shp_nbr = b.shp_nbr   WHERE a.evnt_dt BETWEEN '2016-03-12' AND '2016-03-18'     AND a.par_cons_nbr IN ('A') ) AS dtGROUP BY par_nbr`