Hi,
If you try with PostgreSQL, both queries fail.
You need to use:
SELECT account_id, COUNT(time) as t FROM TABLE_123
GROUP BY account_id HAVING count(time) = 2;
H2 supports using column aliases in "having", but column names have
priority.
Regards,
Thomas
On Friday, August 21, 2015, Taras Fedkiv <[email protected]> wrote:
> Hello,
>
> H2 v1.4.188
> H2 Console (jdbc:h2:mem:test)
>
> Create table and insert few rows in it:
> *CREATE MEMORY TABLE IF NOT EXISTS TABLE_123(account_id BIGINT, time
> BIGINT) NOT PERSISTENT*
>
> *INSERT INTO TABLE_123 (account_id,time) VALUES (10,2)*
> *INSERT INTO TABLE_123 (account_id,time) VALUES (10,2)*
> *INSERT INTO TABLE_123 (account_id,time) VALUES (20,3)*
> *INSERT INTO TABLE_123 (account_id,time) VALUES (20,3)*
>
> 1) *SELECT account_id, COUNT(time) as t FROM TABLE_123 GROUP BY
> account_id HAVING t=2*
> Returns [{account_id=10, t=2}, {account_id=20, t=2}] and its correct
>
> 2)*SELECT account_id, COUNT(time) as time FROM TABLE_123 GROUP BY
> account_id HAVING time=2 *
> Bad Result - returns only [{account_id=10, time=2}],
> Seems it treats 'time' as column but not as alias to 'COUNT(time)'
> function.
> This is incorrect because HAVING filters rows after grouping and column
> 'time' doesn't exist at that stage.
>
> Any help will be highly appreciated.
>
> Thank you,
> Taras
>
> The information in this message may be confidential. It is intended
> solely for
> the addressee(s). If you are not the intended recipient, any disclosure,
> copying or distribution of the message, or any action or omission taken by
> you
> in reliance on it, is prohibited and may be unlawful. Please immediately
> contact the sender if you have received this message in error.
>
> --
> You received this message because you are subscribed to the Google Groups
> "H2 Database" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected]
> <javascript:_e(%7B%7D,'cvml','h2-database%[email protected]');>
> .
> To post to this group, send email to [email protected]
> <javascript:_e(%7B%7D,'cvml','[email protected]');>.
> Visit this group at http://groups.google.com/group/h2-database.
> For more options, visit https://groups.google.com/d/optout.
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
