Group by using LIKE Operator and ignore NULL value

Question

I have written one query to group the tax items, if tax is null, i dont want to display the tax name. How to do this? Please check my sql server statement:

 SELECT     
   'CENTRAL EXCISE DUTY'    AS 'TaxName',
   SUM(TaxAmount)       AS 'Tax'
 FROM
   PMT_InvoiceTaxAttribute
 WHERE 
   InvoiceID = 100 
   AND TaxAttributeName NOT LIKE '%IBR%' 
   AND TaxAttributeName NOT LIKE '%CST%' 
   AND TaxAttributeName NOT LIKE '%VAT%'
 UNION
 SELECT
   'CST'            AS 'TaxName',
   SUM(TaxAmount)       AS 'Tax'
 FROM
   PMT_InvoiceTaxAttribute
 WHERE 
   InvoiceID = 100   
   AND TaxAttributeName LIKE '%CST%'
 UNION
 SELECT
   'VAT'            AS 'TaxName',
   SUM(TaxAmount)       AS 'Tax'
 FROM
   PMT_InvoiceTaxAttribute
 WHERE 
   InvoiceID = 100   
   AND TaxAttributeName LIKE '%VAT%'

This Query gives the Output as follows:

      TaxName                     Tax
   -------------              -----------
  CENTRAL EXCISE DUTY             15000
        CST                       NULL
        VAT                       NULL

Here, I don't want to display the CST and VAT since it has null value.

score 5 · Answer 1 · answered Jun 27 '13 at 05:34

Try this:

SELECT * FROM (
SELECT     
   'CENTRAL EXCISE DUTY'    AS 'TaxName',
   SUM(TaxAmount)       AS 'Tax'
 FROM
   PMT_InvoiceTaxAttribute
 WHERE 
   InvoiceID = 100 
   AND TaxAttributeName NOT LIKE '%IBR%' 
   AND TaxAttributeName NOT LIKE '%CST%' 
   AND TaxAttributeName NOT LIKE '%VAT%'
 UNION
 SELECT
   'CST'            AS 'TaxName',
   SUM(TaxAmount)       AS 'Tax'
 FROM
   PMT_InvoiceTaxAttribute
 WHERE 
   InvoiceID = 100   
   AND TaxAttributeName LIKE '%CST%'
 UNION
 SELECT
   'VAT'            AS 'TaxName',
   SUM(TaxAmount)       AS 'Tax'
 FROM
   PMT_InvoiceTaxAttribute
 WHERE 
   InvoiceID = 100   
   AND TaxAttributeName LIKE '%VAT%') as SomeName
WHERE TAX is NOT NULL

Please note that `UNION` is unnecessary in this case. – Devart Jun 27 '13 at 05:45 — Devart, Jun 27 '13 at 05:45

Joachim Isaksson · Accepted Answer · 2013-06-27T08:30:31.953

Since you're using SQL Server, you can simplify it somewhat using a common table expression and just eliminate the rows you don't want using HAVING at the end;

WITH cte AS (
  SELECT 
    CASE WHEN TaxAttributeName LIKE '%CST%' THEN 'CST' 
         WHEN TaxAttributeName LIKE '%VAT%' THEN 'VAT'
         WHEN TaxAttributeName LIKE '%IBR%' THEN NULL
         ELSE 'CENTRAL EXCISE DUTY' END TaxName,
    TaxAmount Tax
  FROM PMT_InvoiceTaxAttribute
  WHERE InvoiceID = 100
)
SELECT TaxName, SUM(Tax) Tax FROM cte
GROUP BY TaxName
HAVING TaxName IS NOT NULL AND SUM(Tax) IS NOT NULL

An SQLfiddle to test with.

EDIT: Just for completeness, the minimally intrusive way to fix your existing query is to wrap it in another select that removes the rows you're not interested in;

SELECT * FROM (
    ...your query here...
) a_dummy_name_is_required_here
WHERE Tax IS NOT NULL;

Note the slight difference of the first query against the original query, if a row is set to CSTVAT, the original query will count it as both CST and VAT, while the first query with the CTE will count it as CST only. I suspect they're not overlapping, but I thought I'd mention it to be sure.

Devart · Answer 3 · 2013-06-27T05:54:43.283

Try this one -

SELECT *
FROM
(
     SELECT
           TaxName = 'CENTRAL EXCISE DUTY'
         , Tax = SUM(TaxAmount)
     FROM PMT_InvoiceTaxAttribute
     WHERE InvoiceID = 100
          AND TaxAttributeName NOT LIKE '%IBR%'
          AND TaxAttributeName NOT LIKE '%CST%'
          AND TaxAttributeName NOT LIKE '%VAT%'

     UNION ALL

     SELECT 
            TaxName = 
                    CASE WHEN TaxAttributeName LIKE '%CST%'
                              THEN 'CST' 
                              ELSE 'VAT' 
                    END
          , Tax = SUM(TaxAmount)
     FROM PMT_InvoiceTaxAttribute
     WHERE InvoiceID = 100
          AND ( 
                 TaxAttributeName LIKE '%CST%' 
               OR 
                 TaxAttributeName LIKE '%VAT%'  
          )
) t
WHERE Tax IS NOT NULL

Or try this -

;WITH cte AS 
(
     SELECT TaxAttributeName, TaxAmount 
     FROM PMT_InvoiceTaxAttribute
     WHERE InvoiceID = 100
)
SELECT *
FROM
(
     SELECT 
            TaxName = 'CENTRAL EXCISE DUTY'
          , Tax = SUM(TaxAmount)
     FROM cte
     WHERE NOT EXISTS(
         SELECT 1
         FROM (VALUES ('IBR'), ('CST'), ('VAT')) c(filter)  
         WHERE TaxAttributeName LIKE '%' + filter + '%'
     )

     UNION ALL

     SELECT 
            TaxName = 
                    CASE WHEN TaxAttributeName LIKE '%CST%'
                              THEN 'CST' 
                              ELSE 'VAT' 
                    END
          , Tax = SUM(TaxAmount)
     FROM cte
     WHERE ( 
                 TaxAttributeName LIKE '%CST%' 
               OR 
                 TaxAttributeName LIKE '%VAT%'  
          )
) t
WHERE Tax IS NOT NULL

I think your second query is missing a `GROUP BY CASE ... END` on the latter part of the union. At least I can't make it work as is :) — Joachim Isaksson, Jun 27 '13 at 08:35

Gordon · Answer 4 · 2013-06-27T06:05:41.520

An alternative to avoid the UNIONS.

select
    Case 
        When TaxAttributeName LIKE '%CST%' Then 'CST'
        When TaxAttributeName LIKE '%VAT%' Then 'VAT'
        Else 'CENTRAL EXCISE DUTY'
    End 'TaxName',
    SUM(TaxAmount) as 'Tax'
From 
    PMT_InvoiceTaxAttribute
Where
    InvoiceID = 100
    AND TaxAttributeName not like '%IBR%'
Group By
    Case 
        When TaxAttributeName LIKE '%CST%' Then 'CST'
        When TaxAttributeName LIKE '%VAT%' Then 'VAT'
        Else 'CENTRAL EXCISE DUTY'
    End

Although I like Joachim's CTE a little better. Maybe...

with TaxCTE as (
    select
        InvoiceID,
        Case When TaxAttributeName LIKE '%CST%' Then 'CST'
             When TaxAttributeName LIKE '%VAT%' Then 'VAT'
             Else 'CENTRAL EXCISE DUTY'
        End 'TaxName',
        TaxAmount
    From PMT_InvoiceTaxAttribute
    where TaxAttributeName not like '%IBR%')
select TaxName, SUM(TaxAmount) Tax 
from TaxCTE 
where InvoiceId = 100
group by TaxName

Group by using LIKE Operator and ignore NULL value

4 Answers4