| Author |
 Topic  |
|
|
verkley
Starting Member
Netherlands
3 Posts |
 Posted - 11/04/2012 : 09:50:52
|
I have a query that includes a sum. I get multiple rows back, but I only need one row! Can the query be done with only one row and all of the vallues back?
This is the query, and below is the resultset:
select DISTINCT
DPIC300.PERS_NR
, DPIC300.DV_VLGNR
, SUM(CASE WHEN DPID027.vrlfr_mut_kd = 'FIET' THEN DPID027.VRLFR_MUT_UREN ELSE 0 END) OVER (PARTITION BY DPID027.vrlfr_mut_kd) as AG
, SUM(CASE WHEN DPID027.vrlfr_mut_kd = 'PENS' THEN DPID027.VRLFR_MUT_UREN ELSE 0 END) OVER (PARTITION BY DPID027.vrlfr_mut_kd) as AH
from DPIC300
LEFT OUTER JOIN DPID027
on DPIC300.PERS_NR = DPID027.PERS_NR
and DPIC300.DV_VLGNR = DPID027.DV_VLGNR
WHERE dpic300.pers_nr = 1365
GROUP BY DPIC300.pers_nr, DPIC300.dv_vlgnr, DPID027.vrlfr_mut_kd, DPID027.vrlfsrt_kd, DPID027.VRLFR_MUT_UREN
PERS_NR DV_VLGNR AG AH
---------- -------- --------------------------------------------------- ---------------------------------------------------
1.365 1 -10,0000 0,0000
1.365 1 0,0000 -12,0000
1.365 1 0,0000 0,0000
[3 row(s) returned]
[Execution completed] |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47189 Posts |
Posted - 11/04/2012 : 11:31:38
|
do you mean this?
if not post some sample data and show your output
select DISTINCT
DPIC300.PERS_NR
, DPIC300.DV_VLGNR
, SUM(CASE WHEN DPID027.vrlfr_mut_kd = 'FIET' THEN DPID027.VRLFR_MUT_UREN ELSE 0 END) OVER (PARTITION BY DPID027.vrlfr_mut_kd) as AG
, SUM(CASE WHEN DPID027.vrlfr_mut_kd = 'PENS' THEN DPID027.VRLFR_MUT_UREN ELSE 0 END) OVER (PARTITION BY DPID027.vrlfr_mut_kd) as AH
from DPIC300
LEFT OUTER JOIN DPID027
on DPIC300.PERS_NR = DPID027.PERS_NR
and DPIC300.DV_VLGNR = DPID027.DV_VLGNR
WHERE dpic300.pers_nr = 1365
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
verkley
Starting Member
Netherlands
3 Posts |
Posted - 11/04/2012 : 12:37:56
|
The output on the above query from visakh16 is: (I don't know how to put it nicely in a readable table for the forum)
Three rowes and there all from the same person (Pers_nr & DV_vlgnr)
PERS_NR DV_VLGNR AG AH
1.365 1 -10,0000 0,0000
1.365 1 0,0000 -12,0000
1.365 1 0,0000 0,0000
The result I am looking for is this:
PERS_NR DV_VLGNR AG AH
1.365 1 -10,0000 -12,0000
The results are all into one row. And there is no NULL or 0. My intention is to add more than just the two collums (AI, AJ and AK must also be added, but has a different DPID027.vrlfr_mut_kd.
A person has only one Pers_nr, but can have more then one DV_vlgnr due to several different Job's at the same employer. In this case there is only one DV_vlgnr.
The table (DPIC300 outer Join table) has a 3371 rows (different Pers_nr and DV_vlgnr).
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47189 Posts |
|
| |
Topic |
|
|
|
Multiple rows (but I need only one!)
