Received: from dc-mx01.cluster1.charter.net (dc-mx01.cluster0.hsacorp.net [209.225.8.11]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g183lVE02292 for ; Thu, 7 Feb 2002 22:47:31 -0500 (EST) (envelope-from billc@ballydev.com) Received: from [24.205.204.5] (HELO ballydev.com) by dc-mx01.cluster1.charter.net (CommuniGate Pro SMTP 3.5.3) with ESMTP id 6840714 for pgsql-sql@postgresql.org; Thu, 07 Feb 2002 22:55:43 -0500 Message-ID: <3C634A56.20800@ballydev.com> Date: Thu, 07 Feb 2002 19:47:34 -0800 From: Bill Cunningham User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:0.9.4) Gecko/20011128 Netscape6/6.2.1 X-Accept-Language: en-us MIME-Version: 1.0 CC: pgsql-sql@postgresql.org Subject: Re: Help with a SQL query References: <3C62EB58.7050004@kepnet.net> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Archive-Number: 200202/93 X-Sequence-Number: 6521 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 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