CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Eray Balkanli
Eray Balkanli

Reputation: 7990

Hiding a row where value = 0 but count its other column values in total calculations - sql2008

I have tables like that: (C1-C2 varchar(10), C3-Number int)

WaitingData
C1     C2     C3     Number
A      B      1       10
A      B      2       0
A      B      3       4
X      B      4       2

CompletedData
C1     C2     C3     Number
A      B      1       5
A      B      2       2
A      B      3       0
X      B      4       12

I am using the query below to represent the data:

Select wd.C1,wd.C2,wd.C3,wd.Number as NW,cdd.Number as NC
into #AllData
from (Select C1,C2,C3,sum(Number) from WaitingData group by C1,C2,C3) wd
outer apply (Select C1,C2,C3,sum(Number) 
             from CompletedData cd
             where wd.C1=cd.C1 and wd.C2=cd.C2 and wd.C3=cd.C3
            ) cdd


Select * from #AllData
union
Select C1='Total',C2='Total',C3=-1, sum(NW),sum(NW)
from #AllData

This is giving me an output like:

C1      C2      C3     NW     NC
A       B       1      10     5
A       B       2      0      2
A       B       3      4      0
X       B       4      2      12 
Total   Total   -1     16     19

However, I want to hide the rows that has no NW but calculate its regarding values while calculating the Total row (see NC below). The output I want is like:

C1      C2      C3     NW     NC
A       B       1      10     5
A       B       3      4      0
X       B       4      2      12 
Total   Total   -1     16     19

I could not find a way to provide an output like this. Any help would be so appreciated!

------------------------------EDIT---------------------------------------

------------------------------EDIT---------------------------------------

When I have data in the tables like below, the outer apply is not working like I want, it does not include the data A B 2.

WaitingData

C1     C2     C3     Number
A      B      1       10
A      B      3       4
X      B      4       2

CompletedData
C1     C2     C3     Number
A      B      1       5
A      B      2       2
X      B      4       12

And the output would be like:

C1      C2      C3     NW     NC
A       B       1      10     5
A       B       3      4      NULL
X       B       4      2      12 
Total   Total   -1     16     17

In this situation, what can I do to count "2" NC value having by A B 2 on the final result and see NC as 19 instead 17, except inserting all the records that included by CompletedData but WaitingData? (need an efficient way)

Upvotes: 1

Views: 102

Answers (2)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

Wrap the final result with one more select and exclude rows where NW = 0.

select * from
(
Select * from #AllData
union
Select C1='Total',C2='Total',C3=-1, sum(NW),sum(NC)
from #AllData
) t
where NW <> 0

Edit: Using a full join to get all values from both tables.

with t as
(select coalesce(w.c1,c.c1) as c1,coalesce(w.c2,c.c2) as c2,coalesce(w.c3,c.c3) as c3
, coalesce(w.number,0) as nw , coalesce(c.number,0) as nc
from waitingdata w
full join completeddata c on w.c1 = c.c1 and w.c2=c.c2 and w.c3=c.c3) 
select * from 
(select * from t 
union all
Select C1='Total',C2='Total',C3=-1, sum(NW),sum(NC)
from t) x where nw <> 0

Upvotes: 2

Tom H
Tom H

Reputation: 47392

You can do all of this in one query, without temporary tables, intermediate results, subqueries, or UNION by using the ROLLUP operator:

SELECT
    WD.C1,
    WD.C2,
    WD.C3,
    SUM(WD.Number) AS NW,
    SUM(CD.Number) AS NC
FROM
    dbo.WaitingData WD
LEFT OUTER JOIN CompletedData CD ON
    CD.C1 = WD.C1 AND
    CD.C2 = WD.C2 AND
    CD.C3 = WD.C3
GROUP BY
    WD.C1,
    WD.C2,
    WD.C3
WITH ROLLUP
HAVING
    GROUPING_ID(WD.C1, WD.C2, WD.C3) IN (0, 7) AND
    SUM(WD.Number) <> 0

Upvotes: 0

Related Questions