CODEWITHSUNDEEP
mysqlsql
emie
emie

Reputation: 289

SQL Query Case with Where/Having Clause

How do you apply a Where or Having clause to a query? I am having problems with the Having clause. 

DECLARE @dtDate DATE
SET @dtDate = GETDATE();

with EMS as
    (
       select * from ReportingView.WTA where FiscalMonth = DATENAME(MONTH, @dtDate) + ', ' +  DATENAME(YEAR, @dtDate) and ProductGroup = 'AAD'
    )

select 
       [ID]
      ,(CASE 
            WHEN Entitlements <= 0 THEN '0'
            ELSE CAST([Activations] as float) / [Entitlements]
        END) as Utilization 
from EMS
**HAVING  Utilization >= .25**

Upvotes: 0

Views: 1068

Answers (2)

RToyo
RToyo

Reputation: 2877

Both a where and a having clause go at the end of your query. If you have both, then the where comes before the having.

In your case, your having is not working, because having is only to be used with group by. having is essentially a where clause for aggregate values (such as sum, count, etc)

Examples:

WHERE

SELECT
    *
FROM
    EMS
WHERE
    Utilization >= 0.25

HAVING

SELECT
    col1, count(*)
FROM
    EMS
GROUP BY
    col1
HAVING
    count(*) > 10

HAVING and WHERE

SELECT
    col1, count(*)
FROM
    EMS
WHERE
    Utilization >= 0.25
GROUP BY
    col1
HAVING
    count(*) > 10

Edit: This modified query should work for you. I'm not sure why your original query was using a CTE, but I've moved the case logic to the CTE.

with EMS as
(
    select 
        [ID],
        (
            CASE 
                WHEN Entitlements <= 0 THEN '0'
                ELSE CAST([Activations] as float) / [Entitlements]
            END
        ) as Utilization 
    from
        ReportingView.WTA 
    where
        FiscalMonth = DATENAME(MONTH, @dtDate) + ', ' +  DATENAME(YEAR, @dtDate)
        and ProductGroup = 'AAD'
)
select
    *
from
    EMS
where
    Utilizaiton >= .25

Upvotes: 0

Benjamin Racette
Benjamin Racette

Reputation: 172

The HAVING keyword is only used if you are using a GROUP BY too. What you want is a WHERE but you will not be able to reference Utilization unless you wrap it in a sub select.

Upvotes: 1

Related Questions