CODEWITHSUNDEEP
t-sqlhaving
Jar
Jar

Reputation: 2010

TSQL only group a column when a condition is met in another column

The result of this query has multiple transactionCodes. I want to group the transactionCodes only if applyToLineID = '0'. In other words, if the applyToLineID is something other than 0, the transaction codes should not be grouped. I think this can be done with HAVING statement, but I could not get it to work. Below you will find 2 pieces of code, the first piece is my original code, and the 2nd piece is the original code plus my attempt to perform the aforementioned grouping.

Original Code:

SELECT 
ar.arlineitemid,
ar.siteid,
ar.transactioncode,
ar.description,
ar.amount,
ar.balance,
ar.applytolineid,
ar.posteddate,
TC.transactioncode,
TC.arlineitemid

FROM tswdata.dbo.t_arlineitem as AR     

left JOIN

(
SELECT

ar2.transactioncode,
ar2.arlineitemid

FROM tswdata.dbo.t_arlineitem as ar2    

WHERE  

ar2.SiteID = '101'
AND
(AR2.Transactioncode LIKE 'A%'  And
AR2.Transactioncode NOT LIKE 'AS%' And
AR2.Transactioncode NOT LIKE 'AF%' And
AR2.Transactioncode NOT LIKE 'AX%')
) TC

ON
ar.applytolineid = TC.arlineitemid

WHERE  

ar.SiteID = '101'
AND
AR.Transactioncode <> 'A18'

Code where I tried to perform the conditional grouping:

SELECT 
ar.arlineitemid,
ar.siteid,
ar.transactioncode,
ar.description,
ar.amount,
ar.balance,
ar.applytolineid,
ar.posteddate,
TC.transactioncode,
TC.arlineitemid

FROM tswdata.dbo.t_arlineitem as AR     

left JOIN

(
SELECT

ar2.transactioncode,
ar2.arlineitemid

FROM tswdata.dbo.t_arlineitem as ar2    

WHERE  

ar2.SiteID = '101'
AND
(AR2.Transactioncode LIKE 'A%'  And
AR2.Transactioncode NOT LIKE 'AS%' And
AR2.Transactioncode NOT LIKE 'AF%' And
AR2.Transactioncode NOT LIKE 'AX%')
) TC

ON
ar.applytolineid = TC.arlineitemid

WHERE  

ar.SiteID = '101'
AND
AR.Transactioncode <> 'A18'

GROUP BY 
ar.transactioncode,

HAVING
ar.ApplyTolineId = '0'

Upvotes: 0

Views: 44

Answers (2)

mikurski
mikurski

Reputation: 1363

Consider breaking down your problem like this:

You have two sets of data you want to look at - the ones where applyToLineID = '0', and the ones where applyToLineID <> '0'.

You have a query that pulls out the raw information that both sets would use to evaluate the applyToLineID logic.

Your solution for this will probably look something like:

SELECT * FROM
    (select common data, Grouped Values from (common_query) where applyToLineID = 0)
UNION
SELECT * FROM
    (select common data, unGrouped Values from (common_query) where applyToLineID <> 0)

Upvotes: 1

Jim
Jim

Reputation: 3510

You could union two queries: one that includes everything except items where the transaction code is 0, and another that only has the aggregate of the items where the transaction code is 0.

Upvotes: 2

Related Questions