CODEWITHSUNDEEP
sqlsql-server
dimas
dimas

Reputation: 2597

SQL result of sum shows duplicate column values

I've been trying to do alot of sql manipulations just to get the desired result but two days after still no joy...

I have the SQL Result below and am trying to achieve the "Desired Result" table. What it does is to sum up the value of NoScan based on Stype per Branch.

Code    Branch  NoScan  sType
PK001   BF      258     R
PK001   BF      474     N
BO001   BF      435     N
MM006   BF      62      R
LH001   CP      2       F
RK001   CP      1       O
QB001   CP      26      N
TJ001   CP      3       F
GS147   DU      79      O
HR001   DU      5       F
IV002   DU      3       F
NP123   DU      149     O
WC001   EL      30      R
CO100   EL      230     R
CO100   EL      4       F


Desired Result

Branch      F(Count)        R(Count)        N(Count)        O(Count)
BF          0               320             909             0
CP          5               0               26              1
DU          8               0               0               228
EL          435             260             0               0

This is my base query but I did not include the other queries I have come up because they failed anyway to get the desired result.

    ;with deli as 
    (
        SELECT  
                c.Code as Code
                ,dh.Branch as Branch
                ,COUNT(dh.dID) as NoScan
                ,dh.sType as sType
        FROM dbo.tblSDHead dh with (nolock)
            inner join dbo.tblCor c with (nolock) on dh.dID = c.code
            inner join dbo.tblSPD spd with(nolock) on dh.wbno = spd.wbno
        WHERE 
                dh.Branch IN 
                (
                    SELECT DISTINCT code
                    FROM tblloc
                    WHERE coid IN ('1','2')
                )
            and c.isactive = 1
            and c.sshd = 1
            and spd.pddate >= @startdate
            and spd.pddate < @enddate
            and spd.delicomp = 'Y'
            and dh.sType in ('N','O','R','F')
        GROUP BY 
            c.Code
            ,dh.branch
            ,dh.sType
    )
select 
    *
from
    (
        select 
            o.Code
            ,o.Branch
            ,o.NoScan
            ,o.sType
        from
            deli o
    ) overall
order by overall.branch asc

Any help is greatly appreciated! Thanks

Upvotes: 0

Views: 69

Answers (4)

z m
z m

Reputation: 1503

This seems like a wrong approach. In case you ever have more than 4 values in your sType column, then you need to update your query. Display should be handled in presentation layer.

You should try to display the same results with fixed number of columns like this:

select branch, sType, sum(NoScan)
from table
group by branch, sType

Upvotes: 0

Jeremy Real
Jeremy Real

Reputation: 766

You need one more query after overall.

with deli as 
    (
        SELECT  
                c.Code as Code
                ,dh.Branch as Branch
                ,COUNT(dh.dID) as NoScan
                ,dh.sType as sType
        FROM dbo.tblSDHead dh with (nolock)
            inner join dbo.tblCor c with (nolock) on dh.dID = c.code
            inner join dbo.tblSPD spd with(nolock) on dh.wbno = spd.wbno
        WHERE 
                dh.Branch IN 
                (
                    SELECT DISTINCT code
                    FROM tblloc
                    WHERE coid IN ('1','2')
                )
            and c.isactive = 1
            and c.sshd = 1
            and spd.pddate >= @startdate
            and spd.pddate < @enddate
            and spd.delicomp = 'Y'
            and dh.sType in ('NDX','ONX','RDF','FDXE')
        GROUP BY 
            c.Code
            ,dh.branch
            ,dh.sType
    )
,overall as
    (
        select 
            o.Code
            ,o.Branch
            ,o.NoScan
            ,o.sType
        from
            deli o
    ) 
select o.Branch
      ,sum(case when o.sType = 'F' then o.NoScan else null end) as F_Count
      ,sum(case when o.sType = 'R' then o.NoScan else null end) as R_Count
      ,sum(case when o.sType = 'N' then o.NoScan else null end) as N_Count
      ,sum(case when o.sType = 'O' then o.NoScan else null end) as O_Count
from overall o
group by o.Branch

Upvotes: 1

SqlZim
SqlZim

Reputation: 38023

using conditional aggregation, skipping the cte:

select  
    Branch  = dh.Branch
  , F_Count = sum(case when left(dh.sType,1) = 'F' then 1 else 0 end)
  , R_Count = sum(case when left(dh.sType,1) = 'R' then 1 else 0 end)
  , N_Count = sum(case when left(dh.sType,1) = 'N' then 1 else 0 end)
  , O_Count = sum(case when left(dh.sType,1) = 'O' then 1 else 0 end)
  , NoScan  = count(dh.did)
  , sType   = dh.sType
from dbo.tblsdhead dh with (nolock)
    inner join dbo.tblCor c with (nolock) 
      on dh.did = c.code
    inner join dbo.tblspd spd with(nolock) 
      on dh.wbno = spd.wbno
where dh.Branch in (
    select distinct code
    from tblloc
    where coid in ('1','2')
    )
    and c.isactive = 1
    and c.sshd = 1
    and spd.pddate >= @startdate
    and spd.pddate < @enddate
    and spd.delicomp = 'Y'
    and dh.sType in ('ndx','onx','rdf','fdxe')
group by dh.branch

Upvotes: 2

jarlh
jarlh

Reputation: 44696

Use case expressions to do conditional aggregation:

select branch,
       sum(case when sType = 'F' then NoScan else 0 end) as Fcount,
       sum(case when sType = 'N' then NoScan else 0 end) as Ncount,
       sum(case when sType = 'R' then NoScan else 0 end) as Rcount,
       sum(case when sType = 'O' then NoScan else 0 end) as Ocount
from tablename
group by branch

Replace tablename with a sub-query (derived table) that generates your input.

Upvotes: 5

Related Questions