Group by, distinct, PARTITION BY

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_wgt
end) OVER (PARTITION BY a.shp_nbr) as Wgt

from event_history a
inner 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 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
Junior Contributor

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

Junior Contributor

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 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 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

Junior Contributor

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 dt
GROUP BY par_nbr