From: Bill Cunningham <billc@ballydev.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Help with a SQL query
Date: Thu, 07 Feb 2002 19:47:34 -0800
Message-ID: <3C634A56.20800@ballydev.com> (raw)
References: <m3lme5b1e6.fsf@eagle.dansmithnet.com>
<3C62EB58.7050004@kepnet.net>
Mark Nielsen wrote:
> Dan Smith wrote:
>
>> Can someone help me figure out a SQL query to do this?
>>
>> I have a table with 3 columns: date, count, host. It records the
>> number of operations per day by a specific host. There are usually 3
>> or 4 entries per day per host. I can do max(count)-min(count) to
>> figure out how many operations per day were completed. What I need is
>> a query that will output 3 columns: date, host1, host2; the rows will
>> be the per-day counts. For example:
>>
>> Date Host1 Host2
>> ---- ----- -----
>> Feb-2 25 19
>> Feb-3 20 29
>> Feb-4 4 18
>
>
>
> It is a pretty long sql command, but basically,
> 1. select a list of unique dates, then select a list of unique hosts,
> and then select a count for the hits per host per date. I don't think
> it is easy to list it out the way you want since there are an unknown
> number of hosts. I would create an sql query to list it out
> date host value
> instead. Then, use your scripting language to organize the data the way
> you want it. THe problem is you have an unknow number of hosts.
> I would just use a perl script (or python, php), to select the
> information, add it up, and the reformat it to print it out in the way
> you want. I wouldn't bother doing this all in sql. I think some of it
> has to be done in a programming language.
>
> But I am not a total guru, so maybe there is a way.
> Mark
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
To get a count of the total number by host per day you could use:
select date, sum(count), host from <sometable> group by date, host
This should give you something like:
Feb-2 25 host1
Feb-2 19 host2
Feb-3 20 host1
Feb-3 29 host2
A simple perl script can rearrange to suit.
Or maybe I missed something?
- Bill
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: billc@ballydev.com
Subject: Re: Help with a SQL query
In-Reply-To: <3C634A56.20800@ballydev.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox