pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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




view thread (2+ messages)

Message-ID: <3C634A56.20800@ballydev.com>
Permalink:  ../3C634A56.20800@ballydev.com/
Also on:    postgresql.org/message-id/3C634A56.20800@ballydev.com

 · 

reply

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