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