From: Zac <zaccheob@inwind.it>
Subject: Re: How to join several selects
Date: Wed, 24 Aug 2005 17:26:35 +0200
Organization: Hub.Org Networking Services
Lines: 26
Message-ID: <dei3fi$2miq$1@news.hub.org>
References: <430C7A21.9020306@openwired.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
User-Agent: Mozilla Thunderbird 1.0 (X11/20041206)
In-Reply-To: <430C7A21.9020306@openwired.net>
To: pgsql-sql@postgresql.org

Josep Sanmartí wrote:
> Hello,
> I have a 'big' problem:
> I have the following table users(name, start_time, end_time), a new row 
> is set whenever a user logs into a server.  I want to know how many 
> users have logged in EVERYDAY between 2 different dates. The only idea 
> that I have is making several select (one for each day):
>    SELECT COUNT(name) FROM users WHERE start_time between "startDate" 
> and "startDate+1"
>    SELECT COUNT(name) FROM users WHERE start_time between "startDate+1" 
> and "startDate+2"
>    ...
> I would like to know if its possible to make it in 1 sql statement or 
> just which is the best efficient way to solve it.
> By the way, I use Postgres 7.4.
> 
> Thanks!
> 
SELECT
	date_trunc('day', start_time) as day, count(name)
FROM
	users
WHERE
	start_time between "startDate" AND "endDate"
GROUP BY
	day;