On Wed, Oct 26, 2005 at 18:16:13 +0300, Volkan YAZICI <volkan.yazici@gmail.com> wrote:
> And I want to collect the count of sales at hour = 21 and hour = 22.
> For this purpose, I'm using below SELECT query:
>
> => SELECT
> -> (SELECT count(id) FROM sales
> -> WHERE id = 2
> -> AND date_trunc('hour', dt) = '2005-10-25 21:00:00'),
> -> (SELECT count(id) FROM sales
> -> WHERE id = 2
> -> AND date_trunc('hour', dt) = '2005-10-25 22:00:00');
> ?column? | ?column?
> ----------+----------
> 6 | 2
> (1 row)
>
> Isn't it possible to combine these two SELECTs as one. Because one of
> their filters are same: id = 2. I'm just trying to avoid making 2
> scans with nearly same filters.
Use an OR clause when checking the time. You will need to enclose it
in parenthesis because AND binds tighter than OR.
For consecutive hours you could use a range test. (In fact you could use
a range test even for one hour and it might be fasterdepending on your
data and what indexes you have.)