CODEWITHSUNDEEP
sqlsql-server
spas2k
spas2k

Reputation: 519

Referencing AS in the HAVING clause

Is there a way to reference the AS so I can use them in the HAVING clause?

Quick explanation. My organization is grouped up into varying business lines. I'm trying to find the total number of application installs within the organization, the entire bank, and I want to limit the results to only show those apps that have a business line total number greater than 50% of the total.

Thanks for the help!

select adp_id,
(select SUM(total) from dbo.IQCS_AppsByCC b where coctr_L3 = '99990001594' and b.adp_id = a.adp_id)as bl_total,
(select SUM(total) from dbo.IQCS_AppsByCC c where c.adp_id = a.adp_id)as bank_total
from dbo.IQCS_AppsByCC a
where coctr_L3 = '99990001594'
and adp_id IN(19897, 15034, 17381, 13840 )
group by adp_id
HAVING bl_total / bank_total * 100 > 50

Error code 207, SQL state S0001: Invalid column name 'bl_total'.

The potential duplicate question does not have a solution or alternative way to work around the issue, therefore useless.

Upvotes: 1

Views: 136

Answers (4)

paparazzo
paparazzo

Reputation: 45096

I think there is a better way
That first sum is just a repeat of the main where 

 select a.adp_id, sum(total) from as bl_total, b.ttl as bank_total
   from dbo.IQCS_AppsByCC a 
   join ( select adp_id, sum(total) as ttl
            from dbo.IQCS_AppsByCC 
           where adp_id IN (19897, 15034, 17381, 13840)
           group by adp_id 
        ) b
     on b.adp_id = a.adp_id
  where coctr_L3 = '99990001594'
    and a.adp_id IN (19897, 15034, 17381, 13840)
  group by a.adp_id
 HAVING sum(total) * 2 > b.ttl

Upvotes: 1

jarlh
jarlh

Reputation: 44776

Wrap the original query up in a derived table. Then you can use the column aliases in the outer WHERE clause instead of in HAVING. (Same result.)

select * from
(
    select adp_id,
    (select SUM(total) from dbo.IQCS_AppsByCC b where coctr_L3 = '99990001594' and b.adp_id = a.adp_id)as bl_total,
    (select SUM(total) from dbo.IQCS_AppsByCC c where c.adp_id = a.adp_id)as bank_total
    from dbo.IQCS_AppsByCC a
    where coctr_L3 = '99990001594'
    and adp_id IN(19897, 15034, 17381, 13840 )
    group by adp_id
) dt
WHERE bl_total / bank_total * 100 > 50

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269973

Many databases support aliases in the having clause. You can simplify your query, and perhaps this will work (which it probably will not):

select adp_id,
       sum(case when coctr_L3 = '99990001594' then total end) as bl_total,
       sum(total) as bank_total
from dbo.IQCS_AppsByCC a
where adp_id in (19897, 15034, 17381, 13840)
group by adp_id
having bl_total / bank_total * 100 > 50;

In any case, you can switch this to:

having sum(case when coctr_L3 = '99990001594' then total end) > 0.5 * sum(total)

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175766

You could use conditional aggregation and use expression instead of aliases:

select adp_id,
  SUM(CASE WHEN coctr_L3 = '99990001594' THEN total ELSE 0.0 END)as bl_total,
  SUM(total) as bank_total
from dbo.IQCS_AppsByCC a
where coctr_L3 = '99990001594'
and adp_id IN(19897, 15034, 17381, 13840 )
group by adp_id
HAVING 
 SUM(CASE WHEN coctr_L3 = '99990001594' THEN total ELSE 0 END)/SUM(total)*100>50

Upvotes: 0

Related Questions