Re: [DUG]: multi aggregates with clauses??

[Tracey Maule]

Title: Message

thanks for your reply wes, but it doesnt look like that is going to work with interbase (firbird 1.0)   firstly interbase didnt like the [ so i replaced with ( now it doesnt like the select after the from statement, which was going to be the solution to my Q =(     ----- Original Message ----- From: Wes Edwards To: Multiple recipients of list delphi Sent: Monday, March 24, 2003 1:02 PM Subject: RE: [DUG]: multi aggregates with clauses?? I spent 5 minutes looking at this and I don't think it is quite right.  If you want proportions (ie SumofCountL/TotalCount) returned in your result set you might be out of luck on some platforms and may have to resort to calculating the proportions in a reporting field or in a stored proc.  Performance may also be dismal with this approach.  Hopefully this might head you in the right direction though ...   SELECT extract(month from incident.incident_date) AS TheDate,             Count(incident.incident_date) AS TotalCount,             Sum(L.CountL) AS SumOfCountL,             Sum(M.CountM) AS SumOfCountM,             Sum(N.CountN) AS SumOfCountN,             Sum(NLL.CountNull) AS SumOfCountNull FROM incident,             [select incident.ltiormti, Count( incident.ltiormti) as CountL from incident where  incident.ltiormti = 'L']. AS L,             [select incident.ltiormti, Count( incident.ltiormti) as CountM from incident where  incident.ltiormti = 'M']. AS M,             [select incident.ltiormti, Count( incident.ltiormti) as CountN from incident where  incident.ltiormti = 'N']. AS N,             [select incident.ltiormti, Count( incident.ltiormti) as CountNull from incident where  incident.ltiormti is null]. AS NLL WHERE companyid = companyid             and  ((extract(month from incident_date) >= 3 and extract(year from incident_date) >= 2002)             or (extract(month from incident_date) < 3 and (extract (year from incident_date) = 2003))) GROUP BY incident.incident_date ORDER BY incident.incident_date; Cheers Wes Edwards    -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tracey Maule Sent: Monday, 24 March 2003 11:56 a.m. To: Multiple recipients of list delphi Subject: [DUG]: multi aggregates with clauses?? Hi   yes, sql again i have a simple table, which holds a date (incident_date) and a type (ltiormti) of incident.  ltiormti has a value of either L, M, N or null I need to retrieve how many L's occur in each month, over the last 12 months, and how many M's etc.....   I am creating a report (html) which is to chart ratios of each type of incident.  what i was wondering is if i can do something like this without a stored procedure:   Select extract(month from incident_date) as TheDate,          //these bits are the bits i need, but cannot do (select sum(ltiormti) where ltiormti = 'L') as LTI, (select sum(ltiormti) where ltiormti = 'M') as MTI   from incident where companyid = companyid and ((extract(month from incident_date) >= 3 and extract(year from incident_date) >= 2002) or (extract(month from incident_date) < 3 and (extract (year from incident_date) = 2003))) order by incident_date   if so, how? cheers in advance   Tracey Software Developer / Web Master Logis [EMAIL PROTECTED] (025) 213-1065 --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.461 / Virus Database: 260 - Release Date: 10/03/03 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.461 / Virus Database: 260 - Release Date: 10/03/03