pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Help with a SQL query
2+ messages / 2 participants
[nested] [flat]
* Re: Help with a SQL query
@ 2002-02-07 21:02 Mark Nielsen <python@kepnet.net>
2002-02-08 03:47 ` Re: Help with a SQL query Bill Cunningham <billc@ballydev.com>
0 siblings, 1 reply; 2+ messages in thread
From: Mark Nielsen @ 2002-02-07 21:02 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Help with a SQL query
2002-02-07 21:02 Re: Help with a SQL query Mark Nielsen <python@kepnet.net>
@ 2002-02-08 03:47 ` Bill Cunningham <billc@ballydev.com>
0 siblings, 0 replies; 2+ messages in thread
From: Bill Cunningham @ 2002-02-08 03:47 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2002-02-08 03:47 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-02-07 21:02 Re: Help with a SQL query Mark Nielsen <python@kepnet.net>
2002-02-08 03:47 ` Bill Cunningham <billc@ballydev.com>
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