All Forums Database
Phanto 4 posts Joined 03/16
31 Mar 2016
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

 

 

 

dnoeth 4628 posts Joined 11/04
31 Mar 2016

What's the relationship between shp_nbr and par_nbr?
Are there multiple par_nbr per shp_nbr or 1:n or m:n?

Dieter

Phanto 4 posts Joined 03/16
31 Mar 2016

Hi Dieter,
there are many shp_nbr to realated to one par_nbr.
Regards

dnoeth 4628 posts Joined 11/04
31 Mar 2016

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

 

Dieter

Phanto 4 posts Joined 03/16
31 Mar 2016

Thank you so much Dieter.
I tried it before only with grouping but it failed.
however it works now.
 
Thanks a million.

Regards Sven

Phanto 4 posts Joined 03/16
31 Mar 2016

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

dnoeth 4628 posts Joined 11/04
31 Mar 2016

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

 

Dieter

You must sign in to leave a comment.