CODEWITHSUNDEEP
sqlsql-server
John Janssen
John Janssen

Reputation: 323

Add results of a query into one row sql

I have a query that is returning 5 rows with 14 columns. These 5 rows are child records and I need to roll them up to the Parent based off of the parentID. I need to add up the results of each of the columns. Below is the query I am running to return my 5 rows. I am not sure if there is a way I can restructure this to give me my one parent row with all of the results of each column added up, or if I need to do something completely different. Any insight on how to roll it up to the parent would be appreciated.

select 
    Client.ClientName as [Client Name], client.Parent, 
    Sum(pr.snp_dedc) AS [CS], sum(pr.snp_dedY) as [Gar], 
    sum(pr.snp_dedu) as [S], sum(pr.snp_dedo) as [LST],
    sum(pr.snp_dedT) as [SD], sum(pr.snp_dedz) as [k], 
    sum(pr.snp_dedw) as [ESC], sum(pr.snp_ded7) as [ESCP], 
    sum(pr.snp_ded2) as [MISC2], sum(pr.snp_ded3) as [MISC3], 
    sum(pr.snp_dedB) as [CAB] 
FROM 
    pr WITH (NOLOCK, INDEX(IDX_CheckDeductions))
INNER JOIN 
    Client with (nolock) ON pr.ClientID = Client.clientID 
WHERE 
    pr.[snp_wkend] > '1/1/2011 12:00:00 AM'
    AND pr.[snp_wkend] < '12/19/2013 11:38:26 AM' 
    AND (pr.snp_dedc <> 0.00 OR pr.snp_dedY <> 0.00 OR pr.snp_dedu <> 0.00 OR pr.snp_dedo <> 0.00 OR pr.snp_dedT <> 0.00 OR pr.snp_dedz <> 0.00 OR pr.snp_dedw <> 0.00 OR pr.snp_ded7 <> 0.00 OR pr.snp_ded2 <> 0.00 OR pr.snp_ded3 <> 0.00 OR pr.snp_dedB <> 0.00) 
    AND pr.clientid IN (SELECT client.ClientID 
                        FROM client 
                        WHERE client.ClientActive = 1) 
    AND client.clientactive = 1
    AND client.parent = 71 
GROUP BY 
    client.ClientName, client.parent 
ORDER BY 
     client.Parent

I would display the results but it contains sensitive data.

Desired output

parent client name | parent id |sum of cs | sum of gar | sum of s | sum of lst | sum of sd | sum of k | sum of esc | sum of escp | sum of misc2 | sum of misc3 | sum of cab

Upvotes: 0

Views: 105

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You can just remove ClientName from the select and group by clauses:

select client.Parent, 
Sum(pr.snp_dedc) AS [CS], sum(pr.snp_dedY) as [Gar], 
sum(pr.snp_dedu) as [S], sum(pr.snp_dedo) as [LST],
sum(pr.snp_dedT) as [SD], sum(pr.snp_dedz) as [k], 
sum(pr.snp_dedw) as [ESC], sum(pr.snp_ded7) as [ESCP], 
sum(pr.snp_ded2) as [MISC2], sum(pr.snp_ded3) as [MISC3], 
sum(pr.snp_dedB) as [CAB] 
FROM pr WITH (NOLOCK, INDEX(IDX_CheckDeductions))
INNER JOIN Client with (nolock) ON 
pr.ClientID = Client.clientID WHERE pr.[snp_wkend] > '1/1/2011 12:00:00 AM'
and pr.[snp_wkend] < '12/19/2013 11:38:26 AM' AND (pr.snp_dedc <> 0.00 
OR pr.snp_dedY <> 0.00 OR pr.snp_dedu <> 0.00 OR pr.snp_dedo <> 0.00 
OR pr.snp_dedT <> 0.00 OR pr.snp_dedz <> 0.00 
OR pr.snp_dedw <> 0.00 OR pr.snp_ded7 <> 0.00 OR pr.snp_ded2 <> 0.00
OR pr.snp_ded3 <> 0.00 
OR pr.snp_dedB <> 0.00) AND pr.clientid in (select client.ClientID 
from client where client.ClientActive = 1) and client.clientactive = 1
and client.parent = 71
GROUP BY client.parent 
order by client.Parent;

EDIT:

Oh, you just want the parent name. Well, join back to your Client table to get it:

select parent.ClientName as ParentName, client.Parent, 
       Sum(pr.snp_dedc) AS [CS], sum(pr.snp_dedY) as [Gar], 
       sum(pr.snp_dedu) as [S], sum(pr.snp_dedo) as [LST],
       sum(pr.snp_dedT) as [SD], sum(pr.snp_dedz) as [k], 
       sum(pr.snp_dedw) as [ESC], sum(pr.snp_ded7) as [ESCP], 
       sum(pr.snp_ded2) as [MISC2], sum(pr.snp_ded3) as [MISC3], 
       sum(pr.snp_dedB) as [CAB] 
FROM pr WITH (NOLOCK, INDEX(IDX_CheckDeductions)) INNER JOIN
     Client with (nolock) 
     ON pr.ClientID = Client.clientID JOIN
     Client parent
     on client.Parent = parent.ClientId
WHERE pr.[snp_wkend] > '1/1/2011 12:00:00 AM'
and pr.[snp_wkend] < '12/19/2013 11:38:26 AM' AND (pr.snp_dedc <> 0.00 
OR pr.snp_dedY <> 0.00 OR pr.snp_dedu <> 0.00 OR pr.snp_dedo <> 0.00 
OR pr.snp_dedT <> 0.00 OR pr.snp_dedz <> 0.00 
OR pr.snp_dedw <> 0.00 OR pr.snp_ded7 <> 0.00 OR pr.snp_ded2 <> 0.00
OR pr.snp_ded3 <> 0.00 
OR pr.snp_dedB <> 0.00) AND pr.clientid in (select client.ClientID 
from client where client.ClientActive = 1) and
       client.clientactive = 1
and client.parent = 71
GROUP BY client.parent, parent.ClientName 
order by client.Parent;

Upvotes: 1

Related Questions