CODEWITHSUNDEEP
sqlsql-servert-sqlgroup-by
S C Tomas
S C Tomas

Reputation: 39

Group by select case or subselects

I have a list of products and I want to group them by their components. What I've named SIAC is the main component of each final product, and sometimes clients buy this component instead of the product, so I need to have in count both the final component (product's SIAC) and the SIAC as a product itself. That select returns all the SIACs I will need to serve the pending orders.

select      isnull((select case when l.codart like 'SIAC%' 
                then l.codart
                else (select e.codartc from escandallo e where e.codartp = l.codart and e.codartc like 'SIAC%') 
                end as SIAC), 'NO SIAC') as 'REF',
            sum(l.unidades - l.unianulada - l.uniservida) AS PENDING_UNITS
from        LINEPEDI l 
where       ((l.unidades - l.unianulada - l.uniservida) * l.precio) > 0
            and isnull((select case when l.codart like 'SIAC%' 
                then l.codart 
                else (select e.codartc from escandallo e where e.codartp = l.codart and e.codartc like 'SIAC%') 
                end as SIAC), 'NO SIAC') like 'SIAC%'
group by    l.codart
order by    l.codart asc

But I don't know how can I group by "REF"

Grouped by final product (l.codart) returns this

REF         PENDING_UNITS
SIACZM016   300
SIACZM017   1200
SIACZM017   500
SIACZM017   900
SIACZM018   400

So there are 3 final products with the same main component (SIACZM017).

How can I group by this statement?

isnull((select case when l.codart like 'SIAC%' 
                    then l.codart
                    else (select e.codartc from escandallo e where e.codartp = l.codart and e.codartc like 'SIAC%') 
                    end as SIAC), 'NO SIAC')

Thank you very much

Upvotes: 0

Views: 46

Answers (1)

Dale K
Dale K

Reputation: 27202

Just use a lateral join, not only can you group by it, but you avoid repeating it.

select R.Ref
    , sum(l.unidades - l.unianulada - l.uniservida) AS PENDING_UNITS
from LINEPEDI l 
cross apply (
    select isnull((
        select case when l.codart like 'SIAC%' 
            then l.codart 
            else (select e.codartc from escandallo e where e.codartp = l.codart and e.codartc like 'SIAC%') 
            end as SIAC), 'NO SIAC') Ref
) R
where ((l.unidades - l.unianulada - l.uniservida) * l.precio) > 0
and R.Ref like 'SIAC%'
group by R.Ref
order by R.Ref asc;

Upvotes: 1

Related Questions