SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Multiple rows (but I need only one!)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

verkley
Starting Member

Netherlands
3 Posts

Posted - 11/04/2012 :  09:50:52  Show Profile  Reply with Quote
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
52317 Posts

Posted - 11/04/2012 :  11:31:38  Show Profile  Reply with Quote
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/

Go to Top of Page

verkley
Starting Member

Netherlands
3 Posts

Posted - 11/04/2012 :  12:37:56  Show Profile  Reply with Quote
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).
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/04/2012 :  12:49:19  Show Profile  Reply with Quote
sorry cant make out much from your explanation

please post required info in below format and explain your output


http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000