CODEWITHSUNDEEP
sqlwhere-clausealias
user3549784
user3549784

Reputation: 11

SQL using alias in where clause

I know the alias doesn't work but cant figure out to get get the intended result of "and count_of_circs = circ_limit" 

    select libraryid,
           patronid,
           count(circ.id) as count_of_circs,
           (
               select account.circsperpatronmonth 
               from account 
               where circ.libraryid = account.libraryid
           ) as circ_limit
    from circ
    where borrowed >= '2014-04-01'
    and borrowed < '2014-04-19'
    and count_of_circs = circ_limit

    group by patronid,libraryid

Upvotes: 1

Views: 64

Answers (2)

vav
vav

Reputation: 4694

You would need to add a line after "group by"

HAVING count_of_circs = circ_limit

It is not an alias, it is filtering after grouping.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270993

You want a having clause:

select libraryid, patronid,
       count(circ.id) as count_of_circs,
       (select account.circsperpatronmonth
        from account
        where circ.libraryid=account.libraryid
       ) as circ_limit
From circ
where borrowed >= '2014-04-01' and borrowed < '2014-04-19'
group by patronid, libraryid
having count_of_circs = circ_limit;

Upvotes: 2

Related Questions