Re: Help with a SQL query

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