Received: from digitalplanit.wdi.co.uk ([195.13.87.250]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f0NBf5054032 for ; Tue, 23 Jan 2001 06:41:06 -0500 (EST) (envelope-from graham@digitalplanit.com) Received: from Graham (dp0002.demon.co.uk [193.237.187.164]) by digitalplanit.wdi.co.uk (8.9.3/8.9.3) with SMTP id LAA06697 for ; Tue, 23 Jan 2001 11:43:22 GMT From: "Graham Vickrage" To: Subject: grouping by date increments Date: Tue, 23 Jan 2001 11:41:40 -0000 Message-ID: MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPart_000_002F_01C08531.73D05A20" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) X-MIMEOLE: Produced By Microsoft MimeOLE V5.00.2615.200 Importance: Normal X-MS-TNEF-Correlator: X-Archive-Number: 200101/286 X-Sequence-Number: 955 This is a multi-part message in MIME format. ------=_NextPart_000_002F_01C08531.73D05A20 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit I am trying to write a select statement to count the occurences of a particular string between a set of dates. I have written this successfully but need to get the count in time increments such as per day/week/month. At the moment I am doing a select for each increment seperately but figure that as its doing a seqential scan then it may be possible to do it all at once. Has anyone done anything similar that or maybe could recommend a more efficient solution. Thanks Graham current select: SELECT to_char(timestamp('01-Jun-2000'), 'DD-Mon-YYYY'), to_char(timestamp('01-Aug-2000'), 'DD-Mon-YYYY'), count(*) FROM table WHERE date >= timestamp('01-Jun-2000') AND date < timestamp('01-Aug-2000') AND text = 'FOOBAR'; ------=_NextPart_000_002F_01C08531.73D05A20 Content-Type: application/ms-tnef; name="winmail.dat" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="winmail.dat" eJ8+IigLAQaQCAAEAAAAAAABAAEAAQeQBgAIAAAA5AQAAAAAAADoAAEIgAcA GAAAAElQTS5NaWNyb3NvZnQgTWFpbC5Ob3RlADEIAQ2ABAACAAAAAgACAAEG gAMADgAAANEHAQAXAAsAKQAAAAIAJgEBA5AGAOQGAAAlAAAACwACAAEAAAAL ACMAAAAAAAMAJgAAAAAACwApAAAAAAADADYAAAAAAB4AcAABAAAAHAAAAGdy b3VwaW5nIGJ5IGRhdGUgaW5jcmVtZW50cwACAXEAAQAAABYAAAABwIUxbcJo XhQR8FER1JAWAEhUUB+2AAACAR0MAQAAAB4AAABTTVRQOkdSQUhBTUBESUdJ VEFMUExBTklULkNPTQAAAAsAAQ4AAAAAQAAGDgDualsxhcABAgEKDgEAAAAY AAAAAAAAAIunWrxGyNMRkBMASFRQH7bCgAAACwAfDgEAAAACAQkQAQAAAIQC AACAAgAAkQMAAExaRnXjHZJFAwAKAHJjcGcxMjUmMgD4C2BuZwHQNTdPAfcC pAPjAgBjaArAc7BldDAgBxMCgH0KgZJ2CJB3awuAZDQMYA5jAFALAwu1IEkg YaBtIHRyeQuAZxQQSG8gdwUQdGUT4CDtESBsBZAFQHMBkBTwB4BXAjAUggWg dRYiaBUAbyxjYwhwCfBjB5FvZusVEQqxdA3gdQtgBcAVsOsFEBRhYhEwdwnh FRMFQOUX0WQV0XMuCqIKhAqA+xPQEPB2FQAUwhTwA6AW4E8EABUwEsAXkXNm GIBsqnkZMHUFQG4J4GQUgv5nGfEW4haEC4AUEAdxHxG3BQAV8xyTaBPgBCBw BJDJGkF5Lxlxay8EYAIwemgaq0EWxARgFgMT02TebxRSFScCEAXAZQDQIIAf H6cVMSDRFdEdVWZpZ38XURbRFdAgkhTgBCAj+HH3FhEHMRUwYwORFuEDoBTg ryMgISAZMRgQbwQQaQJg/ydRFKAj8CniB0ADICeRAiBtF5AuCuMa9kggoQBw ef8CIBUAI/AtwS2BHGEUYQCQ/m0DEArBJ3MFsSohKmEWge5sHfAJcAWgbRYB HfAVIP8EYCdBAREN4AiQJeIG8B2QlmkCIBqrVBDwbmsQsO0ayUcmUBDwbRq6 F0EXYQsl8hViOhq6U0VMRdRDVBSBXxDiKB9SFbEAbXAoJzAxLUoNFqAtAdA5 oCcpLCAgJ0RELU0CIC1ZjzqROdI3/zkDQXVnOY8DOpYWgygqKSBGUhxPTRQQ AaAq8VdIRcRSRRpDID49H0M4v9M84RFgTkQ/xDxATTx4G0HTFPB4BUBAMCdG T8BPQkFSJzsatBHhAgBGAAsAAYAIIAYAAAAAAMAAAAAAAABGAAAAAAOFAAAA AAAAAwADgAggBgAAAAAAwAAAAAAAAEYAAAAAEIUAAAAAAAADAAeACCAGAAAA AADAAAAAAAAARgAAAABShQAAJ2oBAB4ACYAIIAYAAAAAAMAAAAAAAABGAAAA AFSFAAABAAAABAAAADkuMAAeAAqACCAGAAAAAADAAAAAAAAARgAAAAA2hQAA AQAAAAEAAAAAAAAAHgALgAggBgAAAAAAwAAAAAAAAEYAAAAAN4UAAAEAAAAB AAAAAAAAAB4ADIAIIAYAAAAAAMAAAAAAAABGAAAAADiFAAABAAAAAQAAAAAA AAALAA2ACCAGAAAAAADAAAAAAAAARgAAAACChQAAAQAAAAsAOoAIIAYAAAAA AMAAAAAAAABGAAAAAA6FAAAAAAAAAwA8gAggBgAAAAAAwAAAAAAAAEYAAAAA EYUAAAAAAAADAD2ACCAGAAAAAADAAAAAAAAARgAAAAAYhQAAAAAAAAsAUoAI IAYAAAAAAMAAAAAAAABGAAAAAAaFAAAAAAAAAwBTgAggBgAAAAAAwAAAAAAA AEYAAAAAAYUAAAAAAAACAfgPAQAAABAAAACLp1q8RsjTEZATAEhUUB+2AgH6 DwEAAAAQAAAAi6davEbI0xGQEwBIVFAftgIB+w8BAAAAggAAAAAAAAA4obsQ BeUQGqG7CAArKlbCAABQU1RQUlguRExMAAAAAAAAAABOSVRB+b+4AQCqADfZ bgAAAEM6XFdJTkRPV1NcTG9jYWwgU2V0dGluZ3NcQXBwbGljYXRpb24gRGF0 YVxNaWNyb3NvZnRcT3V0bG9va1xvdXRsb29rLnBzdAAAAAMA/g8FAAAAAwAN NP03AAACAX8AAQAAADgAAAA8TkRCQkpBQkRJTE9QQU9PTUZKSE9NRUNHQ0hB QS5ncmFoYW1AZGlnaXRhbHBsYW5pdC5jb20+AAMABhDDhzjCAwAHEEoCAAAD ABAQAAAAAAMAERAAAAAAHgAIEAEAAABlAAAASUFNVFJZSU5HVE9XUklURUFT RUxFQ1RTVEFURU1FTlRUT0NPVU5UVEhFT0NDVVJFTkNFU09GQVBBUlRJQ1VM QVJTVFJJTkdCRVRXRUVOQVNFVE9GREFURVNJSEFWRVdSSVRURQAAAACDig== ------=_NextPart_000_002F_01C08531.73D05A20--